SQL CHECK Constraint
SQL CHECK Constraint
CHECK constraint is used to limit the range of values in the column.
If you define a CHECK constraint on a single column, then the specific value of the column allows only.
If a table definition CHECK constraint, then the constraint rows based on the values of other columns in a particular column of the limit values.
SQL CHECK constraint CREATE TABLE when
The following SQL when the "Persons" table is created to create a CHECK constraint on the "P_Id" column. CHECK constraints specify "P_Id" column must contain only an integer greater than 0.
MySQL:
(
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:
(
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 plurality of column CHECK constraint, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
(
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 ALTER TABLE when
When the table has been created, create columns for CHECK constraints "P_Id", please use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ADD CHECK (P_Id>0)
To name a CHECK constraint, and define a plurality of column CHECK constraint, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
Undo CHECK constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
DROP CONSTRAINT chk_Person
MySQL:
DROP CHECK chk_Person