WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

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 on a single column, that column allows only specific values.

If a CHECK constraint is defined on a table, the constraint restricts the values in a particular column based on the values of other columns in the row.


SQL CHECK constraint when CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint states that the "P_Id" column must only contain 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 CHECK constraints 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 constraint when ALTER TABLE

When the table has been created, to create a CHECK constraint on the "P_Id" 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 CHECK constraints 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')


Revoke CHECK constraints

To remove 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