Check Constraint

The SQL CHECK Constraint

The SQL CHECK constraint is used to add conditions on a column of a table.

Once you add the check constraint on a column, it ensures that the data entered into the column meets the specified conditions. If a particular record does not meet the conditions, the database will prevent you from inserting or updating that record.

Suppose we have a table CUSTOMERS having a column AGE. We can add a CHECK constraint on this column to ensure that the age entered is always a positive number and not greater than 50 years. If someone tries to input a negative age or an age over 50, the database will reject it, ensuring that your data remains accurate and valid.

Check Constraint on Single Column

To add a check constraint on a column level, we have to specify the check constraint just after the column name during table creation.

Syntax

Following is the syntax to specify the check constraint on a single column −

CREATETABLE table_name (
   column_name data_type CHECK(condition));

Example

In the following query, we are creating a table named CUSTOMERS. Here, we are specifying a column-level check constraint on the AGE column, that allows only those records to be inserted where the age value of the customer is greater than “20” −

Open Compiler

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULLCHECK(AGE>=20),
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Verification

To verify whether the check constraint is added to the AGE column, we can use the following query in the MySQL database −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

The above query will show all the details of the CUSTOMERS table, including how many columns have check constraints and what constraints we have specified in the table as shown below −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	CHECK	employees_chk_1

Now, to verify if the CHECK constraint is working properly, let us insert a record into CUSTOMERS where AGE contains a value less than 20 (does not satisfy the given condition) −

INSERTINTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES(1,'Ramesh',15,'Ahmedabad',2000.00);

The output of the above query is as shown below −

ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

Check Constraint on Multiple Columns

We can also add check constraint on multiple columns of a table by specifying the conditions that must be met for the combination of values in those columns.

Suppose we have a table containing the details of products, including their start and end dates. We can add a CHECK constraint that ensures the end date is always greater than or equal to the start date. In this case, the constraint is checking the values in two columns (start date and end date) within the same row to make sure they follow a specific relationship.

Example

In the following example, we are specifying a column-level check constraint on multiple columns (AGE and SALARY) of the CUSTOMERS table. Here, the AGE column will allow only those records where the AGE is greater than or equal to 20, and the SALARY column will allow only those records where the SALARY is greater than 20000 −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULLCHECK(AGE >=20),
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2)CHECK(SALARY >=20000),PRIMARYKEY(ID));

Verification

To verify whether the check constraint is applied on both the columns, we can use the following query in the MySQL database −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	CHECK	customers_chk_1
customers	CHECK	customers_chk_2

Now, we are inserting values into the CUSTOMERS table where the age is less than 20 and the salary is less than 20000.

INSERTINTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES(1,'Ramesh',15,'Ahmedabad',2000.00);

The above query throws an error because the values passed in the AGE and the SALARY columns are not satisfying the CHECK constraints −

ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

Check Constraint at the Table Level

We must use the check constraint before completing the table creation in order to ensure the check constraint at the table level.

Syntax

Following is the syntax to specify the check constraint on the table level −

CREATETABLE table_name (
   column1 data_type,
   column2 data_type,...,CONSTRAINT constraint_name CHECK(column_name condition_value));

Example

In the following SQL query, we are creating a table PRODUCTS. In here, we are specifying a table level check constraint on the DATE_OF_ORDER column, that allows only those records to be inserted where the DATE_OF_ORDER is less than (before) “2023-02-09” −

CREATETABLE PRODUCTS(
   PID INTNOTNULL,
   PNAME VARCHAR(30),
   DELIVERY_CITY VARCHAR(20),
   DATE_OF_ORDER DateNOTNULL,
   PRICE INT,PRIMARYKEY(PID),CONSTRAINT Constraint_DOO CHECK(DATE_OF_ORDER <='2023-02-09'));

Verification

We can verify the CHECK constraint on the created table using the following SQL query −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='PRODUCTS';

Output

It will show all the details of the created table, including how many columns have check constraints on the table level as shown below −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
products	PRIMARY KEY	PRIMARY
products	CHECK	Constraint_DOO

In here, we are inserting values in the PRODUCTS which have the constraint less than “2023-02-09” on the column DATE_OF_ORDER −

INSERTINTO PRODUCTS VALUES(001,'Nike Shoe','Ranchi','2023-01-11',2000);

Following is the output of the above query −

Query OK, 1 row affected (0.01 sec)

Check Constraint on an Existing Column

We can use the ALTER TABLE statement to add the check constraint on an existing column of the table.

Syntax

Following is the Syntax to add a check-constraint on an existing table −

ALTERTABLE table_name
ADDCONSTRAINT constraint_name CHECK(ColumnName condition_value);

Example

In the following query, we are creating a table named CUSTOMERS −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

To add a check constraint on the AGE column, we are using the following query −

ALTERTABLE CUSTOMERS 
ADDCONSTRAINT Constraint_Age CHECK(AGE >=21);

Verification

To verify whether the check constraint is applied after the table creation, use the following SQL query −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

It will display all of the table’s information, including the constraint we added to the age column −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	CHECK	Constraint_Age

Removing a Check Constraint

If there is a way to add a constraint on a column, then you must also be able to remove the constraint from that column. To do that, you can use the ALTER DROP statement.

Syntax

Following is the syntax to remove a check constraint from the table −

ALTERTABLE table_name 
DROPCONSTRAINT constraint_name;

Example

Following example shows how to drop the check constraint from the CUSTOMERS table created above −

ALTERTABLE CUSTOMERS 
DROPCONSTRAINT Constraint_Age;

Verification

Using the following SQL query, we are verifying whether the constraint is removed −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

We can see that the check constraint added on the age column is removed −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY