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:

Create table employee(
id int not null,
empname nvarchar(255) not null
);

Altering an existing column to add a not null constraint:

Alter table employee
modify empname not null;

UNIQUE : All values in columns is different.

Example:

Create table employee(
id int unique,
empname nvarchar(255)
);

Altering an existing column to add a UNIQUE constraint:

ALTER TABLE Persons
ADD UNIQUE (ID); 

PRIMARY KEY: Is Unique and Not Null .

Example:

Create table employee(
id int primary key,
empname nvarchar(255)
);

FOREIGN KEY : The maintain integrity of data.

Example:

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:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); 

Default constraint in sql server

Altering an existing column to add a default constraint:

ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 1 FOR GenderId

Adding a new column, with default value, to an existing table:

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.

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 }