SQLCHECK Constraint
SQL CHECK Constraint
TheCHECK constraint is used to limit the value range that can be placed in a column.
If you define aCHECK constraint on a column it will allow only certain values for this column.
If you define aCHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
SQL CHECK on CREATE TABLE
The following SQL creates aCHECK constraint on the "Age" column when the "Persons" table is created.TheCHECK constraint ensures that the age of a person must be 18, or older:
MySQL:
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
SQL Server / Oracle / MS Access:
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18)
);
To allow naming of aCHECK constraint, and for defining aCHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
SQL CHECK on ALTER TABLE
To create aCHECK constraint on the "Age" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ADD CHECK (Age>=18);
To allow naming of aCHECK constraint, and for defining aCHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint
To drop aCHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
DROP CONSTRAINT CHK_PersonAge;
MySQL:
DROP CHECK CHK_PersonAge;
