SQL CHECK Constraint

SQL CHECK Constraint

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

If a CHECK constraint is defined for a single column, then this column will only allow specific values.

If a CHECK constraint is defined for a table, then this constraint will limit the values in specific columns.

SQL CHECK Constraint on CREATE TABLE

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

My SQL:

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

SQL Server / Oracle / MS Access:

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

If you need to name the CHECK constraint and define CHECK constraints for multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

SQL CHECK Constraint on ALTER TABLE

If you need to create a CHECK constraint for the 'Id_P' column when the table already exists, please use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (Id_P>0)

If you need to name the CHECK constraint and define CHECK constraints for multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

Revoke CHECK Constraint

To revoke the CHECK constraint, please use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

MySQL:

ALTER TABLE Persons
DROP CHECK chk_Person