SQL CHECK Constraint

A CHECK constraint in SQL is used to enforce data integrity by restricting the values allowed in a column or a combination of columns. It ensures that data inserted or updated meets specific conditions.

Adding CHECK Constraints

Adding CHECK Constraints During Table Creation

You can define CHECK constraints when you initially create a table using the CREATE TABLE statement. This is often the most straightforward approach.

Example: Ensuring Age is 18 or Older

Syntax (MySQL)


CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  CHECK (Age >= 18)
);

Syntax (SQL Server, Oracle, MS Access)


CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT CHECK (Age >= 18)
);

Output

Creates a table named 'Persons'. The CHECK constraint ensures that the 'Age' column will only accept values greater than or equal to 18. Attempts to insert or update with an age less than 18 will result in an error.

Adding Named Constraints and Multi-Column Checks

For better organization and more complex conditions (involving multiple columns), you can name your constraint and specify multiple conditions within the CHECK clause.

Syntax


CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  City VARCHAR(255),
  CONSTRAINT CHK_Person CHECK (Age >= 18 AND City = 'Sandnes')
);

Output

Creates a table named 'Persons' with a named constraint ('CHK_Person'). This constraint requires that 'Age' is 18 or older AND 'City' is 'Sandnes'. Any violation will cause an error.

Adding CHECK Constraints After Table Creation

Constraints can also be added to an already existing table using the ALTER TABLE command.

Syntax


ALTER TABLE Persons
ADD CHECK (Age >= 18);

--Named constraint and multi-column check:
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age >= 18 AND City = 'Sandnes');

Output

Adds a CHECK constraint to the existing 'Persons' table. The constraint will be applied to existing and new data, enforcing the specified condition.

Dropping CHECK Constraints

To remove a CHECK constraint, use the DROP CONSTRAINT statement (for most database systems except MySQL, which uses `DROP CHECK`).

Syntax (SQL Server, Oracle, MS Access)


ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

Syntax (MySQL)


ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Output

The CHECK constraint 'CHK_PersonAge' is removed from the 'Persons' table. The condition is no longer enforced.