SQL CHECK Constraint Example

The constraint CHECK defines a restriction on the values that a column can allow.

  • It limits the value that a column can allow, and aborts the execution in case of other values.
  • It is not supported by the MySQL database server.

In the below example, the column salary is defined using the CHECK keyword, to limit the salary between 5000 and 99000.

Run this on IDE

CREATE TABLE employees (
    employee_id INT NOT NULL PRIMARY KEY,
    employee_name VARCHAR(55) NOT NULL,
    gender CHAR(1),
    birth_date DATE NOT NULL,
    hire_date DATE NOT NULL,
    salary INT NOT NULL CHECK (salary >= 5000 AND salary <= 99000),
    department_id INT FOREIGN KEY REFERENCES departments(department_id)
);

In case the table already exists, it returns an error message, which can be avoided by including the keyword IF NOT EXISTS as shown below.

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT NOT NULL PRIMARY KEY,
    employee_name VARCHAR(55) NOT NULL,
    gender CHAR(1),
    birth_date DATE NOT NULL,
    hire_date DATE NOT NULL,
    salary INT NOT NULL CHECK (salary >= 5000 AND salary <= 99000),
    department_id INT FOREIGN KEY REFERENCES departments(department_id)
);

Overall

We now know when and how to use the CHECK constraint.

Related Links