CONSTRAINT

SQL CONSTRAINT Keyword

The CONSTRAINT keyword is used to define rules that restrict the type of data that can be inserted into a table. Constraints help ensure accuracy consistency and integrity of the data in a database.

SQL ADD CONSTRAINT Syntax

The ADD CONSTRAINT command is used to add constraints after a table has already been created. It can be used to add PRIMARY KEY UNIQUE CHECK and FOREIGN KEY constraints.

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

ALTER TABLE table_name

ADD CONSTRAINT constraint_name constraint_type (column1 column2 ...)

SQL ADD CONSTRAINT Example

MySQL / SQL Server / Oracle / MS Access:

This adds a primary key constraint named PK_Vehicles on the columns VehicleID and Brand.

ALTER TABLE Vehicles
ADD CONSTRAINT PK_Vehicles PRIMARY KEY (VehicleID Brand)

ALTER TABLE Vehicles

ADD CONSTRAINT PK_Vehicles PRIMARY KEY (VehicleID Brand)

Note: SQLite does not support adding constraints after table creation. Constraints must be defined when the table is first created.

SQL DROP CONSTRAINT

The DROP CONSTRAINT command is used to remove existing constraints such as UNIQUE PRIMARY KEY FOREIGN KEY and CHECK.

DROP a UNIQUE Constraint

SQL Server / Oracle / MS Access:

ALTER TABLE Vehicles
DROP CONSTRAINT UC_EngineCode

ALTER TABLE Vehicles

DROP CONSTRAINT UC_EngineCode

MySQL:

ALTER TABLE Vehicles
DROP INDEX UC_EngineCode

ALTER TABLE Vehicles

DROP INDEX UC_EngineCode

DROP a PRIMARY KEY Constraint

SQL Server / Oracle / MS Access:

ALTER TABLE Vehicles
DROP CONSTRAINT PK_Vehicles

ALTER TABLE Vehicles

DROP CONSTRAINT PK_Vehicles

MySQL:

ALTER TABLE Vehicles
DROP PRIMARY KEY

ALTER TABLE Vehicles

DROP PRIMARY KEY

DROP a FOREIGN KEY Constraint

SQL Server / Oracle / MS Access:

ALTER TABLE Registrations
DROP CONSTRAINT FK_VehicleOwner

ALTER TABLE Registrations

DROP CONSTRAINT FK_VehicleOwner

MySQL:

ALTER TABLE Registrations
DROP FOREIGN KEY FK_VehicleOwner

ALTER TABLE Registrations

DROP FOREIGN KEY FK_VehicleOwner

DROP a CHECK Constraint

SQL Server / Oracle / MS Access:

ALTER TABLE Vehicles
DROP CONSTRAINT CHK_EngineLimit

ALTER TABLE Vehicles

DROP CONSTRAINT CHK_EngineLimit

MySQL:

ALTER TABLE Vehicles
DROP CHECK CHK_EngineLimit

ALTER TABLE Vehicles

DROP CHECK CHK_EngineLimit

Note: SQLite does not support dropping constraints using ALTER TABLE. To remove a constraint in SQLite the table must be recreated without the constraint.

SQL CONSTRAINTS Labs