SQL CHECKConstraints

SQL CHECK Constraint

The CHECK constraint is used to limit the range of values in a column.

If you define a CHECK constraint on a single column, that column will only allow specific values.

If you define a CHECK constraint on a table, it can limit the values in certain columns based on the values of other columns in the row.

SQL CHECK Constraint on CREATE TABLE

The SQL below creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the "P_Id" column must only contain integers greater than 0.

MySQL：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

To name a CHECK constraint, and define a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

SQL CHECK Constraint on ALTER TABLE

When the table has already been created, to create a CHECK constraint on the "P_Id" column, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

To name a CHECK constraint, and define a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

DROP CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

MySQL：

ALTER TABLE Persons
DROP CHECK chk_Person
Other Extensions