SQL CHECK Constraint

The CHECK is used to restrict the values that can be placed in a column.

When you define a CHECK constraint on a particular column, you allow only specific values for this column.

When you define a CHECK constraint on a table, you restrict the values in certain columns based on values in other columns in the row.

SQL CHECK on CREATE TABLE

The following SQL statement will create a CHECK constraint on the "salary" column during the creation of the "Employee" table. The CHECK constraint will ensure that the salaries must range from 5000 to 10000. It will prevent salaries from being entered beyond the regular salary range.

• For MySQL, MariaDB :

  CREATE TABLE employee (
    emp_id INT NOT NULL,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    birth_date DATE,
    hire_date DATE,
    salary DOUBLE(12, 2),
    dept_name VARCHAR(80),
  
    CHECK(salry >= 5000 AND salry <= 10000)
  );
  

• For Oracle, SQL Server :

  CREATE TABLE employee (
    emp_id INT NOT NULL,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    birth_date DATE,
    hire_date DATE,
    salary DOUBLE(12, 2) CHECK(salry >= 5000 AND salry <= 10000),
    dept_name VARCHAR(80)
  );
  

If you want to give a name to the CHECK constraint or to define a CHECK constraint on multiple columns, use the following syntax:

• For MySQL, MariaDB, Oracle, SQL Server :

  CREATE TABLE employee (
    emp_id INT NOT NULL,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    birth_date DATE,
    hire_date DATE,
    salary DOUBLE(12, 2),
    dept_name VARCHAR(80),
  
    CONSTRAINT employee_salary_chk CHECK(salary >= 5000 AND salary <= 10000 AND dept_name = 'Development')
  );
  

SQL CHECK on ALTER TABLE

The following SQL statement will create a CHECK constraint on the "salary" column on an already existed "Employee" table.

• For MySQL, MariaDB, Oracle, SQL Server :

  ALTER TABLE employee
    ADD CHECK(salary >= 5000 AND salary <= 10000);
  

If you want to give a name to the CHECK constraint or to define a CHECK constraint on multiple columns, use the following syntax:

• For MySQL, MariaDB, Oracle, SQL Server :

  ALTER TABLE employee
    ADD CONSTRAINT employee_salary_chk CHECK(salary >= 5000 AND salary <= 10000 AND dept_name = 'Development');
  

DROP a CHECK Constraint

You can drop a DEFAULT constraint using the ALTER TABLE statement.

The following SQL statement will drop the CHECK constraint from the "Employee" table.

• For MySQL, MariaDB :

  ALTER TABLE employee
    DROP CHECK employee_salary_chk;
  

• For Oracle, SQL Server :

  ALTER TABLE employee
    DROP CONSTRAINT employee_salary_chk;