SQL CHECK constraints

SQL CHECK constraints

CHECK constraints are used to limit the range of values in a column.

If YOU constraints are defined for a single column, the column allows only specific values.

If you define a CHECK constraint on a table, the constraint limits the value in a particular column based on the values of other columns in the row.

SQL CHECK constraints at CREATE TABLE

The following SQL creates CHECK constraints on the "P_Id" column when the Persons table is created. The CHECK constraint states that the P_Id column must contain only 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 for 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 constraints at ALTER TABLE

When a table has been created, to create a CHECK P_Id in the "Creating" 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 for 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')

Undo the CHECK constraint

To undo the 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