SQL CHECK Constraint

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 column it will allow 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 on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the "student" table is created. The CHECK constraint ensures that the age of a person must be 18, or older:

Example

MySQL
 CREATE TABLE  student(
id int  NOT NULL  ,
Last_name varchar( 50 ) NOT NULL ,
First_name varchar( 50 ) ,
 Age int,

 CHECK  (Age>= 18 )
);

You can click on above box to edit the code and run again.

Example

SQL Server / Oracle / MS Access:

 CREATE TABLE  student(
id int  NOT NULL  ,
Last_name varchar( 50 ) NOT NULL ,
First_name varchar( 50 ) ,
 Age int    CHECK  (Age>= 18 )
);

You can click on above box to edit the code and run again.

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

Example

MySQL / SQL Server / Oracle / MS Access:
 CREATE TABLE  student (
id int  NOT NULL ,
 Last_name varchar( 50 ) NOT NULL ,
First_name varchar( 50 ) ,
Age int,

City varchar( 50 ),
  CONSTRAINT  CHK_student CHECK(Age>=18 AND City='Ranchi')
);

You can click on above box to edit the code and run again.

SQL CHECK on ALTER TABLE

To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:

Example

MySQL / SQL Server / Oracle / MS Access:


 ALTER TABLE  student

 ADD CHECK (Age>=18 ;

You can click on above box to edit the code and run again.

To name a CHECK constraint, and to define a CHECK constraint on multiple columns, use the following SQL syntax:

Example

MySQL / SQL Server / Oracle / MS Access:


 ALTER TABLE  student

ADD CONSTRAINT  CHK_studentAge CHECK (Age>=18 AND City='Ranchi');

You can click on above box to edit the code and run again.

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

Example

MySQL 

 ALTER TABLE  student
 DROP CHECK  CHK_studentAge;

You can click on above box to edit the code and run again.

Example

 SQL Server / Oracle / MS Access:


 ALTER TABLE  student

 DROP CONSTRAINT CHK_studentAge;

You can click on above box to edit the code and run again.