sql_check

SQL CHECK Constraint

SQL CHECK Constraint

The CHECK constraint is a powerful tool in SQL used to enforce domain integrity by limiting the range of values that can be placed in a column or a set of columns. It ensures that data entered into the table adheres to specific business rules or conditions.

If you define a CHECK constraint on a single column, it only allows values for this column that evaluate to TRUE for the specified condition.

If you define a CHECK constraint at the table level, it can limit values in certain columns based on values in other columns within the same row.

SQL CHECK Constraint on CREATE TABLE

You can define a CHECK constraint when you create a table. The following SQL creates a CHECK constraint on the Age column when the Persons table is created. The constraint specifies that the Age column must only include integers greater than or equal to 18.

General Syntax (inline with column definition):

CREATE TABLE Persons (
    PersonID INT PRIMARY KEY,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT CHECK (Age >= 18),  -- Inline CHECK constraint
    City VARCHAR(255)
);

To name a CHECK constraint explicitly, or to define a constraint that involves multiple columns (table-level constraint), use the following syntax. Naming constraints is a good practice as it makes them easier to manage (e.g., to drop or modify later).

General Syntax (named constraint):

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Salary DECIMAL(10, 2),
    Bonus DECIMAL(10, 2),
    HireDate DATE,
    TerminationDate DATE,
    CONSTRAINT CK_Employee_Salary CHECK (Salary > 0), -- Named CHECK constraint for a single column
    CONSTRAINT CK_Employee_Dates CHECK (TerminationDate IS NULL OR TerminationDate > HireDate) -- Named CHECK involving multiple columns
);

Note: The exact syntax for defining named constraints (especially for table-level CHECK constraints) is largely standard across PostgreSQL, SQL Server, Oracle. MySQL started supporting CHECK constraints properly from version 8.0.16; before that, the syntax was accepted but not enforced.

SQL CHECK Constraint on ALTER TABLE

To add a CHECK constraint to an existing table, use the ALTER TABLE statement.

Adding a CHECK constraint to an existing column (e.g., Age in Persons table):

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

This syntax is generally standard. If the table already contains data that violates the new constraint, the ALTER TABLE statement might fail in some database systems, or the constraint might only apply to new or updated rows, depending on the RDBMS.

To DROP a CHECK Constraint

To remove an existing CHECK constraint, you also use the ALTER TABLE statement. You typically need to know the name of the constraint.

Standard SQL (PostgreSQL, SQL Server, Oracle):

ALTER TABLE Persons
DROP CONSTRAINT CK_Person_Age;

MySQL (from version 8.0.16):

ALTER TABLE Persons
DROP CHECK CK_Person_Age; -- Or the system-generated name if not explicitly named

Practical Application: CHECK constraints are vital for maintaining data quality and enforcing business logic at the database level. Examples include:

• Ensuring a product price is always positive.
• Validating that an email address column contains an ’@’ symbol.
• Verifying that an end date is after a start date.
• Restricting a status column to a predefined set of values (e.g., Status IN ('Pending', 'Active', 'Inactive')).

Learning Obstacle: Finding the system-generated name of a constraint if it wasn’t explicitly named can be tricky. Database systems provide ways to query metadata (system catalogs or INFORMATION_SCHEMA) to find constraint names.