The CHECK constraint is used to limit the value range that can be
placed in a column.
If you define a CHECK constraint on a single column it allows only
certain values for this column.
If you define a CHECK constraint on a table it can limit the
values in certain columns based on values in other columns in the row.
SQL CHECK Constraint on
CREATE TABLE
The following SQL creates a CHECK constraint on the "Id"
column when the "Persons" table is created. The CHECK constraint
specifies that the column "Id" must only include integers greater
than 0.
My SQL:
CREATE TABLE Persons
( Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (Id>0) ) |
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
( Id int NOT NULL CHECK (Id>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) |
To allow naming of a CHECK constraint, and for defining a CHECK
constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
( Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (Id>0 AND City='TVL') ) |
SQL CHECK Constraint on
ALTER TABLE
To create a CHECK constraint on the "Id" column when the
table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK (Id>0) |
To allow naming of a CHECK constraint, and for defining a CHECK
constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id>0 AND City='TVL') |
To DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person |
0 comments:
Post a Comment