SQL NOT NULL, UNIQUE, and CHECK Constraints

Constraints are rules you attach to a column that the database enforces automatically on every insert and update. They're what stop bad data from ever making it into your table in the first place, instead of relying on application code to catch every mistake. Three of the most common are NOT NULL, UNIQUE, and CHECK.

NOT NULL

A NOT NULL constraint means a column can never be left empty — every row must have a real value there.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50)
);

-- This fails: name is required
INSERT INTO customers (id, city) VALUES (1, 'Austin');

Result:

ERROR: null value in column "name" violates not-null constraint

UNIQUE

A UNIQUE constraint guarantees no two rows can have the same value in that column — useful for things like email addresses or usernames, where duplicates would be a real bug.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

INSERT INTO customers (id, name, email) VALUES (1, 'Ravi Kumar', 'ravi@example.com');

-- This fails: email already exists
INSERT INTO customers (id, name, email) VALUES (2, 'Ravi K', 'ravi@example.com');

Result:

ERROR: duplicate key value violates unique constraint "customers_email_key"

Note that a primary key is automatically unique (and not null) — UNIQUE is for enforcing uniqueness on other columns that aren't the primary key.

CHECK

CHECK lets you enforce a custom condition on a column's value — anything that would otherwise need to be validated in application code.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2) CHECK (amount > 0)
);

-- This fails: amount must be greater than 0
INSERT INTO orders (id, customer_id, amount) VALUES (501, 1, -50.00);

Result:

ERROR: new row for relation "orders" violates check constraint "orders_amount_check"

Combining Constraints on One Column

Constraints stack — a single column can be NOT NULL, UNIQUE, and have a CHECK all at once:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE CHECK (email LIKE '%@%')
);

This single line now guarantees every customer has an email, no two customers share one, and it at least contains an @ symbol. Pairing constraints like this with a sensible CREATE TABLE design is one of the cheapest ways to prevent entire categories of bugs.

---