SQL | CHECK Constraint

The CHECK constraint in SQL is used to enforce domain integrity by limiting the values that can be inserted into a column or columns. This constraint ensures that the data adheres to a defined criterion or set of criteria, thereby maintaining the data quality within the table.

Syntax:

Here's the basic syntax to add a CHECK constraint while creating a table:

CREATE TABLE table_name (
    column1 datatype [CONSTRAINT constraint_name] CHECK (condition),
    ...
);

You can also add a CHECK constraint to an existing table:

ALTER TABLE table_name 
ADD [CONSTRAINT constraint_name] CHECK (condition);

Examples:

Creating a table with a CHECK constraint:

Suppose we're creating a students table, and we want to ensure that the age of students is between 5 and 30:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT CHECK (age >= 5 AND age <= 30)
);

Creating a table with a named CHECK constraint:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT CONSTRAINT chk_student_age CHECK (age >= 5 AND age <= 30)
);

Adding a CHECK constraint to an existing table:

If you have an existing products table and you want to ensure that the product price is always positive:

ALTER TABLE products
ADD CONSTRAINT chk_positive_price CHECK (price > 0);

Using CHECK constraint on multiple columns:

You can define a CHECK constraint that spans multiple columns. For instance, if you have a table orders with columns start_date and end_date, and you want to ensure end_date is always after start_date:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CHECK (end_date > start_date)
);

Points to Note:

The CHECK constraint can be applied at the column level (for a single column) or at the table level (spanning multiple columns).
Some databases might not enforce CHECK constraints on data that already exists in the table when the constraint is added. Always ensure your existing data meets the conditions before adding a CHECK constraint.
If you attempt to insert or update a record in a way that would violate the CHECK constraint, the operation will fail with an error.
Be cautious when defining overly complex CHECK constraints, as they can impact the performance of insert and update operations.
Always make sure that the logic in your CHECK constraint covers all potential scenarios and does not inadvertently block valid data.

As always, the exact syntax and behavior might slightly vary among different RDBMS. It's advisable to check the official documentation for your database system to understand the specifics.

How to Use CHECK Constraint in SQL:

CREATE TABLE your_table (
   column1 INT,
   column2 VARCHAR(50) CHECK (LEN(column2) >= 3 AND column2 IS NOT NULL)
);

Creating a Table with CHECK Constraint:

CREATE TABLE your_table (
   column1 INT CHECK (column1 > 0),
   column2 VARCHAR(50) CHECK (LEN(column2) >= 3 AND column2 IS NOT NULL)
);

Adding CHECK Constraint to Existing Columns in SQL:

ALTER TABLE your_table
ADD CONSTRAINT chk_column1 CHECK (column1 > 0);

Multiple Conditions with CHECK Constraint in SQL:

CREATE TABLE your_table (
   column1 INT CHECK (column1 > 0 AND column1 < 100),
   column2 VARCHAR(50) CHECK (LEN(column2) >= 3 AND column2 IS NOT NULL)
);

Removing CHECK Constraint in SQL:

ALTER TABLE your_table
DROP CONSTRAINT chk_column1;

Handling NULL Values with CHECK Constraint:

CREATE TABLE your_table (
   column1 INT CHECK (column1 IS NOT NULL),
   column2 VARCHAR(50) CHECK (LEN(column2) >= 3)
);

Alternatives to CHECK Constraint in SQL: CHECK constraints are the primary means for data validation. Alternatives might include triggers or application-level validation.

Checking Existing CHECK Constraints in a Database:

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'CHECK';

SQL CHECK Constraint vs. FOREIGN KEY Constraint: CHECK constraints validate column values based on a condition, while FOREIGN KEY constraints maintain referential integrity between tables.

Using CHECK Constraint for Data Validation in SQL:

CREATE TABLE your_table (
   enrollment_year INT CHECK (enrollment_year >= 2000 AND enrollment_year <= YEAR(GETDATE())),
   -- other columns
);

Checking Constraints During INSERT and UPDATE in SQL: Constraints are checked automatically during data modification operations:

INSERT INTO your_table (column1, column2) VALUES (10, 'abc'); -- Checked against CHECK constraints
UPDATE your_table SET column1 = 5 WHERE column2 IS NULL; -- Checked against CHECK constraints