What is Constraints ?
Constraints is a rule and regulation for entering any data in a table.
Every Table has columns and constraints . It Specified by (i) Create Table (ii) Alter Table .
There are six main constraints that are commonly used in SQL Server:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
NOT NULL : All Values in a columns not empty .
Example:
|
1 2 3 4 5 6 |
Create table employee( id int not null, empname nvarchar(255) not null ); |
Altering an existing column to add a not null constraint:
|
1 2 3 4 |
Alter table employee modify empname not null; |
UNIQUE : All values in columns is different.
Example:
|
1 2 3 4 5 6 |
Create table employee( id int unique, empname nvarchar(255) ); |
Altering an existing column to add a UNIQUE constraint:
|
1 2 3 4 |
ALTER TABLE Persons ADD UNIQUE (ID); |
PRIMARY KEY: Is Unique and Not Null .
Example:
|
1 2 3 4 5 6 |
Create table employee( id int primary key, empname nvarchar(255) ); |
FOREIGN KEY : The maintain integrity of data.
Example:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); |
Altering an existing column to add a FOREIGN KEY constraint:
|
1 2 3 4 |
ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); |
Default constraint in sql server
Altering an existing column to add a default constraint:
|
1 2 3 4 5 |
ALTER TABLE tblPerson ADD CONSTRAINT DF_tblPerson_GenderId DEFAULT 1 FOR GenderId |
Adding a new column, with default value, to an existing table:
|
1 2 3 4 5 |
ALTER TABLE { TABLE_NAME } ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL } CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE } |
he following command will add a default constraint, DF_tblPerson_GenderId.
|
1 2 3 4 5 |
ALTER TABLE tblPerson ADD CONSTRAINT DF_tblPerson_GenderId DEFAULT 1 FOR GenderId |
The insert statement below does not provide a value for GenderId column, so the default of 1 will be inserted for this record.
Insert into tblPerson(ID,Name,Email) values(5,’Sam’,’s@s.com’)
On the other hand, the following insert statement will insert NULL, instead of using the default.
Insert into tblPerson(ID,Name,Email,GenderId) values (6,’Dan’,’d@d.com’,NULL)
To drop a constraint
ALTER TABLE { TABLE_NAME }
DROP CONSTRAINT { CONSTRAINT_NAME }
