PostgreSQL: Implementing CHECK Constraints for Data Integrity

Enforce data integrity in PostgreSQL using CHECK constraints. This guide explains how to define and use CHECK constraints to validate data.

Implementing CHECK Constraints in PostgreSQL

PostgreSQL CHECK constraints enforce data integrity by ensuring that values in a column meet specified conditions. This helps prevent invalid data from being inserted or updated into your tables, maintaining the accuracy and reliability of your database.

Understanding CHECK Constraints

A CHECK constraint is a rule that specifies conditions that must be met by values in a column. These conditions are expressed as boolean expressions. If a value violates the constraint, the database rejects the insertion or update operation.

Creating CHECK Constraints with `CREATE TABLE`

You can define CHECK constraints when you create a table using the `CREATE TABLE` command. The syntax is:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    CONSTRAINT constraint_name CHECK (condition)
);

    

The `CONSTRAINT` clause lets you provide a name for the constraint. The `condition` is a boolean expression that must evaluate to `true` for the constraint to be satisfied. You can use multiple `CHECK` constraints on a single column or table.

Example 1: Creating a Table with CHECK Constraints

(Note: Screenshots from the original text are not included here. Please refer to the original document for visual verification of the commands' success. The descriptions below aim to convey the information present in those screenshots.)

This example creates a `Worker` table with three `CHECK` constraints: one on the `DOB` column, one on `Joined_date`, and one on `Salary`.

CREATE TABLE Worker (
    Worker_id SERIAL PRIMARY KEY,
    Worker_name VARCHAR(40),
    DOB DATE CHECK (DOB > '1900-01-01'),
    Joined_date DATE CHECK (Joined_date > DOB),
    Salary NUMERIC CHECK (Salary > 0)
);

    

Specifying Constraint Names

PostgreSQL automatically names `CHECK` constraints (using a pattern like `table_name_column_name_check`). You can specify your own name using the `CONSTRAINT` clause.

Example 2: Creating a CHECK Constraint with a Custom Name

This example creates an `Orders` table with a named `CHECK` constraint on the `Article_Price` column:

CREATE TABLE Orders (
    Order_no INTEGER,
    Article_name VARCHAR(45),
    CONSTRAINT positive_Article_price CHECK (Article_Price > 0)
);

    

(Note: A screenshot showing how to view constraint information using `\d+ Orders` in `psql` is present in the original text but cannot be included here.)

Adding CHECK Constraints to Existing Tables

Use the `ALTER TABLE ... ADD CONSTRAINT` command to add a `CHECK` constraint to an existing table:

ALTER TABLE Amount_list
ADD CONSTRAINT Amount_Discount_check
CHECK (Amount > 0 AND Discount >= 0 AND Amount > Discount);

    

Follow On

TutorialsArena