T-SQL - ADD CONSTRAINT Keyword

The T-SQL (Transact-SQL) ADD CONSTRAINT statement is used to create a constraint after a table is already created. The ADD CONSTRAINT keyword is mostly used in conjunction with ALTER statement.

ADD UNIQUE Constraint

To enforce UNIQUE constraint on Salary column of table called Employee, the statement is given below:

ALTER TABLE Employee
ADD UNIQUE (Salary);

To provide a name to UNIQUE constraint, and to define a UNIQUE constraint on multiple columns (say Age and Salary), the statement is given below:

ALTER TABLE Employee
ADD CONSTRAINT UC_Employee UNIQUE (Age, Salary);

ADD PRIMARY KEY Constraint

To enforce PRIMARY KEY constraint on EmpID column of table called Employee, the statement is given below:

ALTER TABLE Employee
ADD PRIMARY KEY (EmpID);

To provide a name to PRIMARY KEY constraint, and to define a PRIMARY KEY constraint on multiple columns (say EmpID and Name), the statement is given below:

ALTER TABLE Employee
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmpID, Name);

ADD FOREIGN KEY Constraint

To enforce FOREIGN KEY constraint on EmpID column of table called Contact_Info, the statement is given below:

ALTER TABLE Contact_Info
ADD FOREIGN KEY (EmpID) 
REFERENCES Employee(EmpID);

To provide a name to FOREIGN KEY constraint, and to define a FOREIGN KEY constraint on multiple columns, the statement is given below:

ALTER TABLE Contact_Info
ADD CONSTRAINT FK_Contact_Info 
FOREIGN KEY (EmpID) 
REFERENCES Employee(EmpID);

The statement given below demonstrates how to enforce a FOREIGN KEY constraint on multiple columns (EmpID and PersonName). Please note that, the Employee table must have PRIMARY KEY constraint on these columns - EmpID and Name.

ALTER TABLE Contact_Info
ADD CONSTRAINT FK_Contact_Info 
FOREIGN KEY (EmpID, PersonName) 
REFERENCES Employee(EmpID, Name);

ADD CHECK Constraint

To enforce CHECK constraint on Age column of table called Employee, the statement is given below:

ALTER TABLE Employee
ADD CHECK (Age >= 21);

To provide a name to CHECK constraint, and to define a CHECK constraint on multiple columns (say Age and City), the statement is given below:

ALTER TABLE Employee
ADD CONSTRAINT CHK_Employee 
CHECK (Age >= 21 AND City = 'London');

ADD DEFAULT Constraint

To enforce DEFAULT constraint on City column of table called Employee, the statement is given below:

/* Enforces a DEFAULT constraint named DV_City */
ALTER TABLE Employee
ADD CONSTRAINT DV_City
DEFAULT 'London' FOR City;