PostgreSQL - Constraints

PostgreSQL - Constraints

Constraints are rules enforced on data columns in a table to ensure data integrity, accuracy, and reliability. They prevent invalid data from being entered into the database. Constraints can be defined at the column level or the table level.

The most common constraints in PostgreSQL are:

• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column (or a set of columns) are distinct.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE that uniquely identifies each row in a table.
• FOREIGN KEY: Ensures that a value in a column (or a set of columns) matches a value in a PRIMARY KEY or UNIQUE column of another table, enforcing referential integrity.
• CHECK: Ensures that a value in a column meets a specific condition.
• EXCLUSION: A more general form of UNIQUE constraint, ensuring that if any two rows are compared on specified columns, the comparison with a specified operator will not be true for all pairs.

PRIMARY KEY Constraint

The PRIMARY KEY is the most important constraint. It uniquely identifies each record in a table. A table can have only one primary key, which can consist of a single column or multiple columns (a composite key).

CREATE TABLE departments (
    id SERIAL PRIMARY KEY, -- 'SERIAL' automatically creates a sequence
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department_id INT
);

FOREIGN KEY Constraint

A FOREIGN KEY creates a link between two tables, maintaining referential integrity. It prevents actions that would leave orphaned records. You can specify actions for ON UPDATE and ON DELETE events.

Example

Let’s add a foreign key to our employees table that references the departments table.

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(id)
ON DELETE SET NULL; -- If a department is deleted, set employees' department_id to NULL

Common ON DELETE/ON UPDATE actions include CASCADE (delete/update referencing rows), SET NULL (set the foreign key column to NULL), SET DEFAULT, and RESTRICT (prevent the action, the default).

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition or boolean expression.

Example

Let’s add a CHECK constraint to ensure an employee’s salary is a positive number.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    stock_count INT NOT NULL CHECK (stock_count >= 0)
);

EXCLUSION Constraint

An EXCLUSION constraint guarantees that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. It is a generalization of a UNIQUE constraint. A common use case is preventing overlapping time ranges.

Example

To use exclusion constraints with range types, you first need the btree_gist extension. This example prevents overlapping bookings for the same meeting room.

-- First, enable the required extension once per database
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE meeting_bookings (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    booking_period TSTZRANGE NOT NULL, -- Timestamp with timezone range
    EXCLUDE USING GIST (room_id WITH =, booking_period WITH &&)
);
-- '&&' is the 'overlaps' operator for range types

With this constraint, the database will raise an error if you try to insert a new booking for a room that overlaps in time with an existing booking for the same room.

Dropping Constraints

To drop a constraint, you need to know its name. You can find constraint names using \d table_name in psql or by querying the information_schema.

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example

-- Let's drop the foreign key we created earlier
ALTER TABLE employees DROP CONSTRAINT fk_department;