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 ...)
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)
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
MySQL:
ALTER TABLE Vehicles DROP INDEX UC_EngineCode
DROP a PRIMARY KEY Constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Vehicles DROP CONSTRAINT PK_Vehicles
MySQL:
ALTER TABLE Vehicles DROP PRIMARY KEY
DROP a FOREIGN KEY Constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Registrations DROP CONSTRAINT FK_VehicleOwner
MySQL:
ALTER TABLE Registrations DROP FOREIGN KEY FK_VehicleOwner
DROP a CHECK Constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Vehicles DROP CONSTRAINT CHK_EngineLimit
MySQL:
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
