Kenhuang Academy
  • English

      • 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 TypeDescription
      NOT NULLColumn cannot contain null values
      UNIQUEColumn values must be unique
      PRIMARY KEYPrimary key, uniquely identifies each row
      FOREIGN KEYForeign key, references other tables
      CHECKCheck condition
      EXCLUSIONExclusion 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

      ActionON DELETEON UPDATE
      CASCADECascade deleteCascade update
      SET NULLSet to NULLSet to NULL
      SET DEFAULTSet to default valueSet to default value
      RESTRICTPrevent deletionPrevent update
      NO ACTIONDeferred checkDeferred 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