SQL CHECK Constraint

The CHECK Constraint is used to enable a condition to check the values range that a column can hold in a relation in a table.

SQL CHECK Constraint Conditions

• A CHECK constraint can not be defined on a SQL VIEW.
• A CHECK constraint can not include a SUBQUERY.

CHECK Constraint Syntax

CREATE TABLE table_name (
   column1 datatype   [NULL | NOT NULL],
   column2 datatype    [NULL | NOT NULL],
   ...,
   ...,
   CONSTRAINT constraint_name CHECK (column_name condition)
);

EXAMPLE: SQL CHECK Constraint

SQL /Oracle/MS Access

Below mentioned query access in SQL server, Oracle, and MS Access server:

CREATE TABLE CUSTOMERS (
   CUSTOMER_ID INT   NOT NULL,
   NAME VARCHAR (100)    NOT NULL,
   EMAIL VARCHAR (100),
   ADDRESS CHAR (200),
   AGE INT   CHECK (AGE>=20)
);

MySQL Server

CREATE TABLE CUSTOMERS (
   CUSTOMER_ID INT   NOT NULL,
   NAME VARCHAR (100)    NOT NULL,
   EMAIL VARCHAR (100),
   ADDRESS CHAR (200),
   AGE INT,
   CONSTRAINT myCheck CHECK (AGE>=20)
);

ALTER TABLE a CHECK Constraint

ALTER table a CHECK Constraint means that if the CUSTOMERS table has already been created in a database, You just want to add a CHECK constraint to AGE column; the query mentioned below:

ALTER TABLE CUSTOMERS
ADD CONSTRAINT myCheck CHECK (AGE>=20);

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL commond:

ALTER TABLE CUSTOMERS
DROP CONSTRAINT myCheck;