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