SQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK

SQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK

Constraints enforce rules on data in tables, ensuring accuracy, consistency, and integrity at the database level. They are the last line of defense against invalid data.

PRIMARY KEY

Uniquely identifies each row. Combines UNIQUE and NOT NULL:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

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

FOREIGN KEY

Enforces referential integrity between tables:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

Referential Actions:

• CASCADE: Propagate the change to child rows
• RESTRICT: Prevent the operation if child rows exist
• SET NULL: Set the foreign key column to NULL
• SET DEFAULT: Set to the column's default value

UNIQUE

Ensures all values in a column or combination are distinct:

ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

-- Multi-column unique
ALTER TABLE subscriptions
ADD CONSTRAINT uq_user_plan UNIQUE (user_id, plan_id);

UNIQUE allows multiple NULLs in most databases, while PRIMARY KEY does not allow any NULLs.

CHECK

Validates that values satisfy a boolean expression:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) CHECK (price > 0),
  discount DECIMAL(3,2) CHECK (discount BETWEEN 0 AND 1),
  start_date DATE,
  end_date DATE,
  CONSTRAINT valid_dates CHECK (end_date > start_date)
);

NOT NULL

The simplest constraint, preventing NULL values:

ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Best Practices

• Define constraints during CREATE TABLE rather than adding them later when possible
• Name all constraints explicitly for clearer error messages and easier migrations
• Use CHECK constraints for business rules unlikely to change
• Always add foreign keys to maintain referential integrity
• Consider the performance impact of foreign keys on high-volume write operations

Constraints are essential for building reliable databases that prevent invalid data regardless of which application writes to the tables.