PostgreSQL Constraints

Types of Constraints

id SERIAL PRIMARY KEY

FOREIGN KEY (user_id)
REFERENCES users(id)

email VARCHAR(100) UNIQUE

username VARCHAR(50) NOT NULL

🔹 PRIMARY KEY Constraint

PRIMARY KEY uniquely identifies each record in a table. It automatically creates a unique index and doesn't allow NULL values. Every table should have a primary key.

-- Single column primary key
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2)
);

-- Composite primary key (multiple columns)
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- Insert valid data
INSERT INTO products (name, price)
VALUES ('Laptop', 999.99);

-- This will fail (duplicate primary key)
-- INSERT INTO products (product_id, name, price)
-- VALUES (1, 'Mouse', 25.99);

INSERT 0 1

🔹 FOREIGN KEY Constraint

FOREIGN KEY creates a link between two tables, ensuring referential integrity. It prevents actions that would destroy links between tables and ensures data consistency across related tables.

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

-- Child table with foreign key
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- Insert data
INSERT INTO customers (name) VALUES ('John Doe');
INSERT INTO orders (customer_id, order_date)
VALUES (1, '2024-10-14');

-- This will fail (customer_id 999 doesn't exist)
-- INSERT INTO orders (customer_id, order_date)
-- VALUES (999, '2024-10-14');

INSERT 0 1
INSERT 0 1

🔹 UNIQUE Constraint

UNIQUE ensures all values in a column are different. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns can contain NULL values.

-- Single column unique
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE NOT NULL
);

-- Composite unique constraint
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50),
    warehouse_id INTEGER,
    UNIQUE (sku, warehouse_id)
);

-- Insert valid data
INSERT INTO users (email, username)
VALUES ('[email protected]', 'john_doe');

-- This will fail (duplicate email)
-- INSERT INTO users (email, username)
-- VALUES ('[email protected]', 'jane_doe');

INSERT 0 1

🔹 NOT NULL Constraint

NOT NULL ensures a column cannot have NULL values. This is useful for required fields where data must always be provided during insertion or updates.

-- NOT NULL constraint
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)              -- Can be NULL
);

-- Insert valid data
INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');

-- This will fail (first_name is required)
-- INSERT INTO employees (last_name, email)
-- VALUES ('Smith', '[email protected]');

-- Query to check
SELECT * FROM employees;

employee_id | first_name | last_name | email
------------+------------+-----------+------------------
          1 | John       | Doe       | [email protected]

🔹 CHECK Constraint

CHECK constraint validates data based on a condition. It ensures values meet specific criteria before being inserted or updated, providing custom validation rules.

-- CHECK constraint examples
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) CHECK (price > 0),
    discount INTEGER CHECK (discount >= 0 AND discount <= 100),
    stock INTEGER CHECK (stock >= 0)
);

-- Multiple conditions
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER CHECK (age >= 18 AND age <= 65),
    salary NUMERIC(10, 2) CHECK (salary > 0),
    department VARCHAR(50) CHECK (department IN ('IT', 'HR', 'Sales'))
);

-- Insert valid data
INSERT INTO products (name, price, discount, stock)
VALUES ('Laptop', 999.99, 10, 50);

-- This will fail (negative price)
-- INSERT INTO products (name, price, discount, stock)
-- VALUES ('Mouse', -25.00, 5, 100);

INSERT 0 1

🔹 DEFAULT Constraint

DEFAULT provides automatic values when no value is specified during insertion. This is useful for timestamps, status flags, and other fields with standard initial values.

-- DEFAULT constraint
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    views INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    is_published BOOLEAN DEFAULT FALSE
);

-- Insert without specifying defaults
INSERT INTO posts (title, content)
VALUES ('My First Post', 'Hello World!');

-- Insert with some defaults overridden
INSERT INTO posts (title, content, status, is_published)
VALUES ('Published Post', 'This is live!', 'published', TRUE);

-- Query to see defaults
SELECT * FROM posts;

post_id | title          | status    | views | is_published
--------+----------------+-----------+-------+--------------
      1 | My First Post  | draft     |     0 | f
      2 | Published Post | published |     0 | t

🔹 Adding Constraints to Existing Tables

You can add constraints after table creation using ALTER TABLE:

-- Add PRIMARY KEY
ALTER TABLE users
ADD PRIMARY KEY (user_id);

-- Add FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

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

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

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

-- Drop constraint
ALTER TABLE products
DROP CONSTRAINT check_price;