SQL CONSTRAINT Keyword

In SQL, constraints are rules enforced on a table's data to ensure its integrity and accuracy. They prevent invalid or inconsistent data from being inserted or updated into the database. Constraints can be added when a table is created or later, using the `ALTER TABLE` statement.

Adding Constraints

Adding a Constraint After Table Creation

The ADD CONSTRAINT clause within an ALTER TABLE statement is used to add a new constraint to an existing table. This is a very helpful way to modify your database schema and add new validation rules to the data. You specify the constraint type (primary key, unique, foreign key, or check) and the column(s) it applies to. Constraints help to maintain the consistency and accuracy of your database.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column1, column2, ...);
      

Replace constraint_type with the type of constraint (e.g., PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK).

Example: Adding a Primary Key Constraint

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
      

(A primary key constraint named 'PK_Person' is added to the 'Persons' table, using 'ID' and 'LastName' as a composite key.)
      

Dropping Constraints

Dropping Constraints

The DROP CONSTRAINT statement is used to remove constraints from a table. Use caution, as this can affect data integrity.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
      

--For primary key constraints:
ALTER TABLE table_name DROP PRIMARY KEY;

--For other constraints (UNIQUE, FOREIGN KEY, CHECK):
ALTER TABLE table_name DROP INDEX constraint_name;
      

Replace table_name with your table name and constraint_name with the name of the constraint to be removed.

Examples of Dropping Constraints

-- SQL Server, Oracle, MS Access:
ALTER TABLE Persons DROP CONSTRAINT UC_Person;
-- MySQL:
ALTER TABLE Persons DROP INDEX UC_Person;
      

(The unique constraint 'UC_Person' is dropped.)
      

-- SQL Server, Oracle, MS Access:
ALTER TABLE Persons DROP CONSTRAINT PK_Person;
-- MySQL:
ALTER TABLE Persons DROP PRIMARY KEY;
      

(The primary key constraint 'PK_Person' is dropped.)
      

-- SQL Server, Oracle, MS Access:
ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;
-- MySQL:
ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;
      

(The foreign key constraint 'FK_PersonOrder' is dropped.)
      

-- SQL Server, Oracle, MS Access:
ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;
-- MySQL:
ALTER TABLE Persons DROP CHECK CHK_PersonAge;
      

(The CHECK constraint 'CHK_PersonAge' is dropped.)