PostgreSQL Constraints

Overview

Constraints are used to limit the type and range of data in tables, ensuring data accuracy and reliability. PostgreSQL supports multiple types of constraints.

Constraint Types

Constraint Type	Description
NOT NULL	Column cannot contain null values
UNIQUE	Column values must be unique
PRIMARY KEY	Primary key, uniquely identifies each row
FOREIGN KEY	Foreign key, references other tables
CHECK	Check condition
EXCLUSION	Exclusion constraint

NOT NULL Constraint

When Creating Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Modifying Existing Table

-- Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- Remove NOT NULL constraint
ALTER TABLE users ALTER COLUMN username DROP NOT NULL;

UNIQUE Constraint

Single Column Unique

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

Multi-Column Unique (Composite)

CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

Named Unique Constraint

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50),
    CONSTRAINT unique_sku UNIQUE (sku)
);

Modifying Existing Table

-- Add unique constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- Drop unique constraint
ALTER TABLE users DROP CONSTRAINT unique_email;

PRIMARY KEY Constraint

Single Column Primary Key

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

Composite Primary Key

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

Named Primary Key Constraint

CREATE TABLE products (
    id SERIAL,
    name VARCHAR(100),
    CONSTRAINT pk_products PRIMARY KEY (id)
);

FOREIGN KEY Constraint

Basic Foreign Key

-- Parent table
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Child table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id)
);

Complete Foreign Key Syntax

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Foreign Key Action Options

Action	ON DELETE	ON UPDATE
CASCADE	Cascade delete	Cascade update
SET NULL	Set to NULL	Set to NULL
SET DEFAULT	Set to default value	Set to default value
RESTRICT	Prevent deletion	Prevent update
NO ACTION	Deferred check	Deferred check

Adding Foreign Key to Existing Table

ALTER TABLE employees
ADD CONSTRAINT fk_department
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
    ON DELETE SET NULL;

CHECK Constraint

Single Column Check

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0)
);

Multi-Column Check

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    start_date DATE,
    end_date DATE,
    CHECK (end_date >= start_date)
);

Named Check Constraint

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    salary DECIMAL(10, 2),
    CONSTRAINT check_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT check_salary CHECK (salary > 0)
);

Adding Check Constraint

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

EXCLUSION Constraint

Exclusion constraints ensure that no two rows have matching values on specified columns.

-- Create extension first
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Create non-overlapping time range constraint
CREATE TABLE room_reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    reserved_at TSRANGE NOT NULL,
    EXCLUDE USING GIST (
        room_id WITH =,
        reserved_at WITH &&
    )
);

-- Insert test data
INSERT INTO room_reservations (room_id, reserved_at)
VALUES (1, '[2024-01-15 10:00, 2024-01-15 12:00)');

-- This will fail (time overlap)
INSERT INTO room_reservations (room_id, reserved_at)
VALUES (1, '[2024-01-15 11:00, 2024-01-15 13:00)');

Viewing Table Constraints

-- Using psql command
\d table_name

-- Query system tables
SELECT
    tc.constraint_name,
    tc.constraint_type,
    kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'users';

-- View check constraint definitions
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass;

Dropping Constraints

-- Drop named constraint
ALTER TABLE users DROP CONSTRAINT unique_email;

-- Drop primary key
ALTER TABLE users DROP CONSTRAINT users_pkey;

-- Drop foreign key
ALTER TABLE employees DROP CONSTRAINT fk_department;

Disabling and Enabling Constraints

-- Disable triggers (including foreign key checks)
ALTER TABLE employees DISABLE TRIGGER ALL;

-- Enable triggers
ALTER TABLE employees ENABLE TRIGGER ALL;

-- Note: PostgreSQL doesn't support directly disabling constraints
-- Need to drop and recreate, or use NOT VALID option
ALTER TABLE employees
ADD CONSTRAINT fk_department
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
    NOT VALID;

-- Validate constraint later
ALTER TABLE employees VALIDATE CONSTRAINT fk_department;

Best Practices

1. Always use primary keys: Every table should have a primary key
2. Name constraints: Use meaningful constraint names for maintenance
3. Index foreign keys: Create indexes on foreign key columns for performance
4. Use CHECK moderately: Complex business logic belongs in the application layer
5. Document: Record the business meaning of constraints