SQL CONSTRAINT Keyword

ADD CONSTRAINT

The 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 SQL ALTER statement.

ADD UNIQUE Constraint

To enforce UNIQUE constraint on Salary column of table called Employee, the SQL code 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 SQL code 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 SQL code 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 SQL code 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 SQL code 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 SQL code 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 apply 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.

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  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 SQL code 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 SQL code 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 SQL code is given below:

MySQL

ALTER TABLE Employee
ALTER City SET DEFAULT 'London';

OR

ALTER TABLE Employee
ALTER COLUMN City SET DEFAULT 'London';

SQL Server

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

Oracle

ALTER TABLE Employee
MODIFY City DEFAULT 'London';

DROP CONSTRAINT

The 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 SQL code can be used:

MySQL

ALTER TABLE Employee
DROP INDEX UC_Employee;

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Employee
DROP CONSTRAINT UC_Employee;

DROP a PRIMARY KEY Constraint

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

MySQL / Oracle

ALTER TABLE Employee
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access

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 SQL code is given below:

MySQL

ALTER TABLE Contact_Info
DROP FOREIGN KEY FK_Contact_Info;

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Contact_Info
DROP CONSTRAINT FK_Contact_Info;

DROP a CHECK Constraint

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

MySQL

ALTER TABLE Employee
DROP CHECK CHK_Employee;

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Employee
DROP CONSTRAINT CHK_Employee;

DROP DEFAULT Constraint

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

MySQL

ALTER TABLE Employee
ALTER City DROP DEFAULT;

OR

ALTER TABLE Employee
ALTER COLUMN City DROP DEFAULT;

SQL Server

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

Oracle

ALTER TABLE Employee 
MODIFY City DEFAULT NULL;