(learn how to create CHECK constraints to ensure data integrity.)
In this lesson, you will learn how to use the MySQL CHECK constraint to ensure that values stored in a column or group of columns satisfy a specified Boolean expression. The purpose of the CHECK constraint in SQL is to enforce domain integrity by ensuring that all values in a column or group of columns satisfy a specified condition. It serves as a data validation tool at the database level, allowing you to define rules that data must meet before it can be inserted or updated in the table.
Key Purposes of the CHECK Constraint:
- Data Integrity: Ensures that only valid data, which meets specific criteria, is entered into the database. For example, a
CHECKconstraint can prevent negative values in asalarycolumn or ensure that theagecolumn contains only values greater than or equal to 18. - Enforcing Business Rules: Implements business logic directly in the database schema. For instance, a
CHECKconstraint can enforce that theend_dateof a project is always later than thestart_date. - Simplifying Application Logic: Reduces the need for extensive validation logic in application code by moving basic validation to the database level. This helps maintain consistency across different applications accessing the same database.
- Improving Data Quality: Helps maintain high data quality by preventing invalid data from being stored. For example, a
CHECKconstraint can ensure that theemailcolumn contains valid email formats.
Syntax and Usage
Here's the basic syntax for creating a CHECK constraint:
CONSTRAINT constraint_name
CHECK (expression)
[ENFORCED | NOT ENFORCED]
- First, specify the name for the check constraint that you want to create after the
CONSTRAINTkeyword. If you omit the constraint name, MySQL automatically generates a name with the following convention:table_name_chk_n - In this convention,
nis an ordinal number such as 1,2, and 3. For example, the automatically generated names ofCHECKconstraints of thepartstable will beparts_chk_1,parts_chk_2, and so on. - Second, specify a Boolean
expressionwhich must be evaluated toTRUEorUNKNOWNfor each row of the table inside the parentheses after theCHECKkeyword. - Third, optionally specify the enforcement clause to indicate whether the check constraint is enforced:
- Use
ENFORCEDor omit theENFORCEDclause to create and enforce the constraint. - Use
NOT ENFORCEDto create the constraint but not enforce it. - As mentioned earlier, you can define a
CHECKconstraint as a table constraint or column constraint. - A table
CHECKconstraint can reference multiple columns whereas the columnCHECKconstraint can refer to the only column where it is defined.
MySQL CHECK constraint examples
1) Creating CHECK constraints as column constraints
The following CREATE TABLE statement creates a new table called parts:
CREATE TABLE parts (
part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10,2 ) NOT NULL CHECK (cost >= 0),
price DECIMAL(10,2) NOT NULL CHECK (price >= 0)
);NOTE
cost DECIMAL(10,2) NOT NULL CHECK (cost >= 0): This line defines thecostcolumn, which is intended to store the cost of each part. TheDECIMAL(10,2)data type is used here, meaning the column can store numbers with up to 10 digits, including 2 digits after the decimal point, allowing for precise representation of costs. TheNOT NULLconstraint ensures that every part must have a cost value specified, and theCHECK (cost >= 0)constraint ensures that this value cannot be negative, enforcing that costs are non-negative numbers.price DECIMAL(10,2) NOT NULL CHECK (price >= 0): Similarly, thepricecolumn is designed to store the selling price of each part, with the sameDECIMAL(10,2)data type for precision. It also includes theNOT NULLconstraint, requiring a price to be specified for every part, and aCHECK (price >= 0)constraint to ensure that prices are non-negative.
Because we did not explicitly specify the names of the CHECK constraints, MySQL automatically generated names for them. To view the table definition with the CHECK constraint name, you use the SHOW CREATE TABLE statement:
SHOW CREATE TABLE parts;
The output indicates that MySQL generated the names (parts_chk_1 and parts_chk_2) for the check constraints.
After creating CHECK constraints, if you insert or update a value that causes the Boolean expression to be false, MySQL rejects the change and issues an error.
This statement inserts a new row into the parts table:
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',0,-100);MySQL issued an error:
Error Code: 3819. Check constraint 'parts_chk_2' is violated.Because the value of the price column is negative which causes the expression price > 0 evaluates to FALSE that results in a constraint violation.
2) Creating CHECK constraints as a table constraints
First, drop the parts table:
DROP TABLE IF EXISTS parts;Then, create a new parts table with one more table CHECK constraint:
CREATE TABLE parts (
part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10,2 ) NOT NULL CHECK (cost >= 0),
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
CONSTRAINT parts_chk_price_gt_cost
CHECK(price >= cost)
);The following new clause defines a table CHECK constraint that ensures the price is always greater than or equal to the cost:
CONSTRAINT parts_chk_price_gt_cost CHECK(price >= cost)Because we explicitly specify the name of the CHECK constraint, MySQL creates the new constraint with the specified name.
Here is the definition of the parts table:
SHOW CREATE TABLE parts;
The table CHECK constraint appears at the end of the table definition after the column list.
This statement attempts to insert a new part whose price is less than the cost:
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',200,100);Here is the error due to the constraint violation:
Error Code: 3819. Check constraint 'parts_chk_price_gt_cost' is violated.3. Adding a check constraint to a table
To add a check constraint to an existing table, you use the ALTER TABLE ... ADD CHECK statement:
ALTER TABLE table_name
ADD CHECK (expression);Code languageIf you want to explicitly specify the name of the CHECK constraint, you can use the ALTER TABLE ... ADD CONSTRAINT ... CHECK statement:
ALTER TABLE table_name
ADD CONSTRAINT contraint_name
CHECK (expression);For example, the following statement adds a CHECK constraint to the partstable:
ALTER TABLE parts
ADD CHECK (part_no <> description);This CHECK constraint prevents you from having the part_no identical to the description.
For example, the following INSERT statement will be rejected:
INSERT INTO parts
VALUES('A','A',100,120);Output:
ERROR 3819 (HY000): Check constraint 'parts_chk_3' is violated4. Removing a check constraint from a table
To remove a CHECK constraint from a table, you use the ALTER TABLE ... DROP CHECK statement:
ALTER TABLE table_name
DROP CHECK constraint_name;For example, the following statement removes the CHECK constraint parts_chk_3 from the parts table:
ALTER TABLE parts
DROP CHECK parts_chk_3;Code language: Summary
- Use
CHECKconstraints to ensure values stored in a column satisfy a Boolean condition. - Use the
CHECK(expression)to define aCHECKconstraint. - Use the
ALTER TABLE ... ADD CHECKto add aCHECKconstraint to a table. - Use the
ALTER TABLE ... DROP CHECKto remove aCHECKconstraint from a table.