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.
To add a check constraint on a column level, we have to specify the check constraint just after the column name during table creation.
Following is the syntax to specify the check constraint on a single column −
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” −
To verify whether the check constraint is added to the AGE column, we can use the following query in the MySQL database −
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) −
ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.
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.
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 −
To verify whether the check constraint is applied on both the columns, we can use the following query in the MySQL database −
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.
ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.
We must use the check constraint before completing the table creation in order to ensure the check constraint at the table level.
Following is the syntax to specify the check constraint on the table level −
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” −
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 −
Query OK, 1 row affected (0.01 sec)
We can use the ALTER TABLE statement to add the check constraint on an existing column of the table.
Following is the Syntax to add a check-constraint on an existing table −
In the following query, we are creating a table named CUSTOMERS −
To add a check constraint on the AGE column, we are using the following query −
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
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.
Following is the syntax to remove a check constraint from the table −
Following example shows how to drop the check constraint from the CUSTOMERS table created above −
Using the following SQL query, we are verifying whether the constraint is removed −
TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY customers PRIMARY KEY PRIMARY