PostgreSQL Constraints & Keys

Primary Key

• Purpose: Uniquely identifies each row in a table. One per table.

• Characteristics: Implicitly UNIQUE and NOT NULL. Creates unique index.

• Use: Essential for table relationships and efficient lookups.

sql

-- Single column
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- Auto-incrementing
    username VARCHAR(50)
);

-- Composite primary key (multiple columns)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)  -- Both together must be unique
);

-- Add to existing table
ALTER TABLE users ADD PRIMARY KEY (id);

Foreign Key

• Purpose: Enforces referential integrity between tables.

• Characteristics: Links to another table's primary/unique key. Prevents orphan records.

• Use: Maintains data consistency in relationships.

sql

-- Basic foreign key
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),  -- References users.id
    total DECIMAL(10,2)
);

-- With explicit constraint name
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT,
    product_id INT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE,  -- Delete order_items when order deleted
    FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE SET NULL  -- Set to NULL if product deleted
);

-- Add later
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_user 
    FOREIGN KEY (user_id) REFERENCES users(id);

UNIQUE

• Purpose: Ensures all values in column(s) are distinct.

• Characteristics: Allows multiple NULLs (unless combined with NOT NULL). Creates index.

• Use: For columns that must be unique but aren't primary keys.

sql

-- Single column unique
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,  -- Each email must be unique
    username VARCHAR(50) UNIQUE
);

-- Composite unique (combination must be unique)
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE(user_id, role_id)  -- User can't have same role twice
);

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

CHECK

• Purpose: Validates data meets specific conditions.

• Characteristics: Boolean expression that must evaluate to TRUE.

• Use: Business rule enforcement, data validation.

sql

-- Single column check
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0),  -- Must be positive
    discount DECIMAL(5,2) CHECK (discount >= 0 AND discount <= 100)
);

-- Multi-column check
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CHECK (end_date > start_date)  -- End date must be after start
);

-- Complex check
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    birth_date DATE,
    hire_date DATE,
    salary DECIMAL(10,2),
    CHECK (
        hire_date > birth_date + INTERVAL '18 years' AND
        salary >= 0
    )
);

NOT NULL

• Purpose: Prevents NULL values in column.

• Characteristics: Simplest constraint. No index created.

• Use: Ensures mandatory data is provided.

sql

-- Column level
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),  -- Can be NULL
    email VARCHAR(255) NOT NULL
);

-- Add to existing column
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

-- Remove constraint
ALTER TABLE customers ALTER COLUMN email DROP NOT NULL;

DEFAULT

• Purpose: Provides default value when column not specified in INSERT.

• Characteristics: Not strictly a constraint but behaves like one.

• Use: Set sensible defaults, auto-timestamps, status codes.

sql

-- Common defaults
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),  -- Current timestamp
    is_active BOOLEAN DEFAULT TRUE,
    login_count INT DEFAULT 0,
    preferences JSONB DEFAULT '{}'::JSONB  -- Empty JSON object
);

-- Expression as default
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    log_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    data JSONB,
    processed BOOLEAN DEFAULT FALSE
);

-- Change default
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT FALSE;

-- Function as default (requires function to be immutable)
CREATE OR REPLACE FUNCTION generate_user_code() RETURNS TEXT AS $$
BEGIN
    RETURN 'USER_' || floor(random() * 1000000)::TEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    user_code TEXT DEFAULT generate_user_code()
);

Combining Constraints

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(20) UNIQUE NOT NULL,  -- Multiple constraints
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    category_id INT REFERENCES categories(id) ON DELETE SET NULL
);

Constraint Management

sql

-- View constraints
SELECT conname, contype, consrc 
FROM pg_constraint 
WHERE conrelid = 'products'::regclass;

-- Drop constraint
ALTER TABLE products DROP CONSTRAINT products_price_check;

-- Disable/Enable constraint (useful for bulk loads)
ALTER TABLE products DISABLE TRIGGER ALL;  -- Disable all triggers/constraints
-- Bulk insert operations...
ALTER TABLE products ENABLE TRIGGER ALL;  -- Re-enable

Key Points:

1. Primary Key: One per table, unique + not null, essential for relationships

2. Foreign Key: Maintains referential integrity between tables

3. UNIQUE: Ensures uniqueness, allows NULLs, creates index

4. CHECK: Custom validation rules, boolean expression

5. NOT NULL: Simplest constraint, ensures data presence

6. DEFAULT: Provides value when not specified, not retroactive

Constraints are enforced at the database level, ensuring data integrity regardless of application logic. They're fundamental to relational database design.