SQL CHECK Constraint

Summary: in this tutorial, you will learn how to use the SQL CHECK constraint to ensure data in columns meet specific conditions.

Introduction to SQL CHECK constraint #

In SQL, a check constraint ensures data in one or more columns within a table meet a specific condition. The CHECK constraints help you maintain data accuracy by limiting the value columns can store.

Here’s the syntax for defining a CHECK constraint for a column:

CREATE TABLE table_name (
    column1 data_type CHECK(boolean_expression)
    ...
);Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the CHECK constraint in the column definition when creating the table. When you insert or update values, they must pass the boolean_expression.

When you define a CHECK constraint for a column, the CHECK constraint can check the values for that column only. In other words, the boolean_expression can only access values of the column where you define the CHECK constraint.

To define a CHECK constraint for multiple columns, you define it as a table constraint:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    CHECK (boolean_expression)
);Code language: SQL (Structured Query Language) (sql)

In this syntax, the boolean_expression can access the values of all the columns of the table.

Defining a CHECK constraint as a column constraint #

First, create a table called offers that stores job offers:

CREATE TABLE IF NOT EXISTS offers (
  offer_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  offer_date DATE NOT NULL,
  job_id INT NOT NULL,
  candidate VARCHAR(255) NOT NULL,
  salary DECIMAL(19, 2) NOT NULL CHECK (salary > 0),
  FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE
);Code language: SQL (Structured Query Language) (sql)

Try it

In this table, we define a CHECK constraint for the salary to ensure that it is always greater than zero:

salary DECIMAL(19, 2) NOT NULL CHECK (salary > 0)Code language: SQL (Structured Query Language) (sql)

Second, attempt to insert a row into the salary column of the offers table with the value of zero:

INSERT INTO
  offers (offer_date, job_id, candidate, salary)
VALUES
  ('1994-05-07', 1, 'William Giet', 0);Code language: SQL (Structured Query Language) (sql)

Try it

The database system will issue an error like this:

ERROR:  new row for relation "offers" violates check constraint "offers_salary_check"Code language: SQL (Structured Query Language) (sql)

The output indicates that the new row does not meet the constraint offers_salary_check.

Note that the offers_salary_check is a generated constraint name by the database system. It may vary depending on the database system..

Third, insert a row with a valid salary into the offers table:

INSERT INTO
  offers (offer_date, job_id, candidate, salary)
VALUES
  ('1994-05-07', 1, 'William Giet', 8300);Code language: SQL (Structured Query Language) (sql)

Try it

Finally, retrieve data from the offers table:

SELECT
  offer_date,
  job_id,
  candidate,
  salary
FROM
  offers;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 offer_date | job_id |  candidate   | salary
------------+--------+--------------+---------
 1994-05-07 |      1 | William Giet | 8300.00Code language: SQL (Structured Query Language) (sql)

Defining a CHECK constraint as a table constraint #

First, create a table called job_postings to store the opening job postings:

CREATE TABLE job_postings (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_title VARCHAR(100) NOT NULL,
  description TEXT,
  min_salary DEC(10, 2) NOT NULL,
  max_salary DEC(10, 2) NOT NULL,
  posting_date DATE,
  closing_date DATE,
  CHECK (
    min_salary > 0
    AND min_salary <= max_salary
  ),
  CHECK (closing_date >= posting_date)
);Code language: SQL (Structured Query Language) (sql)

In this example, we define two CHECK constraints as table constraints, which include multiple multiple columns.

The following CHECK constraint ensures that the minimum salary is greater than zero and less than or equal to the maximum salary:

CHECK (
  min_salary > 0
  AND min_salary <= max_salary
)Code language: SQL (Structured Query Language) (sql)

The following CHECK constraint ensures that the posting date is earlier than the closing date:

CHECK (closing_date >= posting_date)Code language: SQL (Structured Query Language) (sql)

Assigning CHECK constraint names #

SQL allows you to assign an explicit name to a CHECK constraint via the CONSTRAINT clause:

CONSTRAINT constraint_name 
CHECK(boolean_expression)Code language: SQL (Structured Query Language) (sql)

The constraint name will help you clarify the error message returned by the database system when the constraint violation occurs. For example:

First, create a table called hires with a CHECK constraint:

CREATE TABLE hires (
  hire_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  offer_id INT NOT NULL,
  hire_date DATE NOT NULL,
  start_date DATE NOT NULL,
  CONSTRAINT check_start_date CHECK (start_date >= hire_date)
);Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we define a CHECK constraint called check_start_date to ensure that the start date must be on or after the hire date.

Second, insert a new row into the hires table with the start date before the hire date:

INSERT INTO
  hires (offer_id, hire_date, start_date)
VALUES
  (1, '1994-06-07', '1994-06-01');Code language: SQL (Structured Query Language) (sql)

Try it

The database system will issue the following error:

ERROR:  new row for relation "hires" violates check constraint "check_start_date"Code language: SQL (Structured Query Language) (sql)

The error message includes the CHECK constraint name check_start_date so that you can use to troubleshoot.

Note that your database system may issue a slightly different error message.

Summary #

Use a CHECK constraint to validate data based on a Boolean expression.

Databases #

Was this tutorial helpful ?