SQL Keyword CONSTRAINT

The CONSTRAINT keyword is used to add or delete a constraint on a table.

  • The keyword ADD CONSTRAINT is used to add a new constraint.
  • The keyword DROP CONSTRAINT is used to delete an existing constraint from a table on database systems like SQL SERVER, Oracle, and MS Access.
  • In MySQL, we use the DROP keyword in a different way to delete constraints.

So, the syntax for adding a constraint is the same across these database systems but has some minor differences in delete constraint syntax.

ADD CONSTRAINT

The below SQL adds a constraint to a table.

Run this on IDE

ALTER TABLE users
ADD CONSTRAINT PK_User PRIMARY KEY (id, name);

DROP CONSTRAINT - SQL Server, Oracle, and MS Access

The below SQL statements deletes a constraint from a table, where the syntax remains the same for any constraint.

ALTER TABLE users
DROP CONSTRAINT UC_User;
ALTER TABLE users
DROP CONSTRAINT PK_User;
ALTER TABLE orders
DROP CONSTRAINT FK_UserOrder;
ALTER TABLE users
DROP CONSTRAINT CHK_UserAge;

DROP CONSTRAINT - MySQL

The below SQL statement deletes a constraint from a table, where the keywords we use for each constraint are different.

ALTER TABLE users
DROP INDEX UC_User;
ALTER TABLE users
DROP PRIMARY KEY;
ALTER TABLE orders
DROP FOREIGN KEY FK_UserOrder;
ALTER TABLE users
DROP CHECK CHK_UserAge;

Related Links