SQL Keys & Constraints

SQL organises keys and constraints into four categories based on what they protect or provide. Each category has a distinct purpose in keeping your database reliable.

A PRIMARY KEY uniquely identifies every row in a table. No two rows can share the same primary key value, and it can never be NULL.

Every table should have exactly one primary key. It can be a single column (simple key) or a combination of columns (composite key). The database automatically creates an index on the primary key for fast lookups.

-- Single-column primary key
CREATE TABLE students (
    student_id  INT          PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150)
);

-- Composite primary key (defined at table level)
CREATE TABLE enrollments (
    student_id  INT,
    course_id   INT,
    enrolled_on DATE,
    PRIMARY KEY (student_id, course_id)
);

How it works: step by step

Worked Trace: INSERT attempts on the students table

A FOREIGN KEY links a column in one table to the PRIMARY KEY of another. It ensures you can't reference a row that doesn't exist.

Foreign keys enforce referential integrity: the guarantee that every relationship in your database points to something real. You cannot add an order for a customer who does not exist, and you cannot delete a customer who still has orders (unless you use CASCADE).

CREATE TABLE orders (
    order_id    INT  PRIMARY KEY,
    customer_id INT,
    amount      DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- With cascade options
FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE    -- delete orders when customer is deleted
    ON UPDATE CASCADE;   -- update orders if customer_id changes

How it works: step by step

Worked Trace: INSERT attempts on the orders table

CASCADE options explained

A UNIQUE constraint ensures no two rows have the same value in the specified column(s). Unlike PRIMARY KEY, a UNIQUE column can hold NULL.

Use UNIQUE for columns that are natural identifiers but not the primary key, such as email addresses, usernames, or phone numbers. A table can have multiple UNIQUE constraints but only one PRIMARY KEY.

CREATE TABLE users (
    user_id  INT          PRIMARY KEY,
    email    VARCHAR(200) UNIQUE,        -- inline
    username VARCHAR(50),
    phone    VARCHAR(20),
    UNIQUE (username),                   -- table level
    UNIQUE (phone)
);

How it works: step by step

Worked Trace: INSERT attempts on the users table

NOT NULL ensures a column always has a value. It can never be left empty (NULL). It is the simplest and most common constraint.

By default, every column in SQL accepts NULL unless you explicitly add NOT NULL. Use it for fields that must always be present: names, dates, status codes, prices, and so on.

CREATE TABLE products (
    product_id   INT           PRIMARY KEY,
    product_name VARCHAR(200)  NOT NULL,   -- must be provided
    price        DECIMAL(8,2)  NOT NULL,   -- must be provided
    description  TEXT                        -- nullable (optional)
);

How it works: step by step

Worked Trace: INSERT attempts on the products table

A CHECK constraint lets you define a custom rule that every row must satisfy. If the condition evaluates to false, the row is rejected.

CHECK is the most flexible constraint. You can write any valid SQL expression. Common uses include ensuring age is within range, salary is above minimum, or status is one of a fixed set of values.

CREATE TABLE employees (
    emp_id   INT          PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    age      INT          CHECK (age >= 18 AND age <= 65),
    salary   DECIMAL(10,2) CHECK (salary > 0),
    status   VARCHAR(20)  CHECK (status IN ('active', 'inactive', 'on_leave'))
);

How it works: step by step

Worked Trace: INSERT attempts on the employees table

DEFAULT automatically fills in a value when no value is provided during INSERT. It does not restrict data, it supplies it.

DEFAULT is the only constraint that adds data rather than rejecting it. It is useful for timestamps (created_at), boolean flags (is_active), counters, or any field with a sensible starting value.

CREATE TABLE orders (
    order_id    INT          PRIMARY KEY,
    customer_id INT          NOT NULL,
    status      VARCHAR(20)  DEFAULT 'pending',
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    quantity    INT          DEFAULT 1,
    is_paid     BOOLEAN      DEFAULT FALSE
);

How it works: step by step

Worked Trace: INSERT attempts on the orders table

An INDEX is a lookup structure that speeds up queries on a column. It does not restrict data; it makes finding it faster.

Without an index, the database must scan every row (a full table scan) to find matching values. An index works like a book's index: you jump straight to the right page instead of reading every page. The trade-off is that indexes use extra storage and slow down INSERT and UPDATE operations slightly.

-- Create a regular index
CREATE INDEX idx_orders_customer
ON orders (customer_id);

-- Create a unique index (same effect as UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_email
ON users (email);

-- Composite index (for queries that filter on both columns)
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

-- Drop an index
DROP INDEX idx_orders_customer;

How it works: step by step

Worked Trace: query performance with and without an index

All seven keys & constraints at a glance:

* FOREIGN KEY allows NULL unless combined with NOT NULL. UNIQUE INDEX does not allow duplicates.