CHECK constraint

When developing applications, an important consideration is data validation. Data entered into the database must satisfy all the business rules defined by analysts.

Check constraints are conditions or sets of conditions that must be satisfied by data values before an insert or update operation can occur. They are used to ensure that data meets certain criteria, for example, an employee's department ID must be a positive integer. A constraint violation occurs if the check condition is not met by any DML statement. You already know a little bit about check constraint. Let's take a more detailed look at this topic

Creating a check constraint

Check constraints can be defined at the column or table level in the CREATE TABLE statement.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY, 
    full_name VARCHAR(255) NOT NULL,
    dept_id INT CHECK (dept_id > 0),
    salary INT,
    bonus INT
); ---column definition

CREATE TABLE employees (
    employee_id INT PRIMARY KEY, 
    full_name VARCHAR(255) NOT NULL,
    dept_id INT,
    salary INT,
    bonus INT,
    CHECK (dept_id > 0)
    ); ---table definition

The database will automatically generate a name for the constraint if none is provided. To view the table definition and check the constraint name, use the show table statement.

show CREATE TABLE employees 

We can see from the image above that the constraint we created was automatically given the name 'employee_chk_1'. A name can be explicitly provided for the check constraint as follows:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY, 
    full_name VARCHAR(255) NOT NULL,
    dept_id INT,
    salary INT,
    bonus INT,
    CONSTRAINT chk_employee_dept_id CHECK (dept_id > 0)
    );

Add a check constraint to an existing table

The ALTER TABLE statement is used to add a check constraint to an existing table.

ALTER TABLE employees ADD CONSTRAINT chk_invalid_dept_id CHECK (dept_id > 0 and dept_id < 20);

Multiple check constraints can reference a single column. Also, a check constraint can reference multiple columns. Continuing with our employee example, we can add the following constraints.

ALTER TABLE employees ADD CONSTRAINT chk_salary_gt_bonus CHECK (salary > bonus);

ALTER TABLE employees ADD CONSTRAINT chk_moderate_bonus CHECK (salary + bonus < salary * 1.3);

Constraint violation

For any insert or update, if the constraint expression is violated, an error message is thrown and the change is rejected.

An attempt was made to insert a row with a dept_id value of 0. The boolean expression dept_id > 0 evaluated as false for this value, hence, the change was rejected.

Also, a similar error message may be encountered when you try to add a constraint to an existing table with data. If any existing row in the table does not satisfy the constraint expression, then the alter table command will fail.

We can see from the image above that the only row in the table has a dept_id of 0, hence the constraint addition failed.

Dropping a constraint

To drop a check constraint, we use the alter table statement.

ALTER TABLE employees DROP CHECK chk_moderate_bonus;

Conclusion

Check constraints are used by the database engine to validate data and satisfy business rules. Check constraints can be added when you create the table using the CREATE TABLE statement, or to an already existing table using the ALTER TABLE statement.

Depending on the requirement, data validation can be done on the database layer or in the application layer within the application code. Using check constraints provides a clean way of enforcing business rules and also helps to avoid the pitfall of developing an application that has data validation code everywhere — in Java screen controllers, database constraints, JavaScript snippets, if-else statements, etc.