The ADD CONSTRAINT command is utilized to establish a constraint after the creation of a table.
Here’s an example SQL statement that adds a constraint named “PK_Person”, which is a PRIMARY KEY constraint on multiple columns (ID and LastName).
| ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); |
The DROP CONSTRAINT command is employed to remove a constraint, such as UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK, from a table.
To remove a UNIQUE constraint, employ the following SQL:
SQL Server / Oracle / MS Access:
| ALTER TABLE Persons DROP CONSTRAINT UC_Person; |
MySQL:
| ALTER TABLE Persons DROP INDEX UC_Person; |
To remove a PRIMARY KEY constraint, utilize the following SQL:
SQL Server / Oracle / MS Access:
| ALTER TABLE Persons DROP CONSTRAINT PK_Person; |
MySQL:
| ALTER TABLE Persons DROP PRIMARY KEY; |
For removing a FOREIGN KEY constraint, execute the following SQL statement:
SQL Server / Oracle / MS Access:
| ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder; |
MySQL:
| ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder; |
To eliminate a CHECK constraint, employ the following SQL:
SQL Server / Oracle / MS Access:
| ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge; |
MySQL:
| ALTER TABLE Persons DROP CHECK CHK_PersonAge; |