SQL Server - CONSTRAINT Keyword

ADD CONSTRAINT

The SQL Server (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;

DROP CONSTRAINT

The SQL Server (Transact-SQL) DROP CONSTRAINT statement is used to delete a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint.

DROP a UNIQUE Constraint

To drop UC_Employee UNIQUE constraint from table called Employee, the below mentioned statement can be used:

ALTER TABLE Employee
DROP CONSTRAINT UC_Employee;

DROP a PRIMARY KEY Constraint

To drop PK_Employee PRIMARY KEY constraint from table called Employee, the statement is given below:

ALTER TABLE Employee
DROP CONSTRAINT PK_Employee;

DROP a FOREIGN KEY Constraint

To drop FK_Contact_Info FOREIGN KEY constraint from table called Contact_Info, the statement is given below:

ALTER TABLE Contact_Info
DROP CONSTRAINT FK_Contact_Info;

DROP a CHECK Constraint

To drop CHK_Employee CHECK constraint from table called Employee, the statement is given below:

ALTER TABLE Employee
DROP CONSTRAINT CHK_Employee;

DROP DEFAULT Constraint

To drop DEFAULT constraint from table called Employee, the statement is given below:

/* Drops a DEFAULT constraint named DV_City */
ALTER TABLE Employee
DROP CONSTRAINT DV_City;