Module F-6·15 min read

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK — letting the database enforce your rules so application code does not have to.

Written by Jatin Jain Saraf · Architect of SupraScan

F-6 — Constraints and Data Integrity

Q: A software team is designing a database schema for a social media platform. They have `users` and `posts` tables, where `posts.author_id` is a foreign key referencing `users.id`. The product requirement states that when a user account is deleted, their posts should not be automatically removed, but instead, the posts should be explicitly reviewed by a moderator for potential re-assignment to an 'anonymous' user or archival. Which `ON DELETE` strategy for the `posts.author_id` foreign key best supports this architectural requirement?

C. ON DELETE RESTRICT — The `ON DELETE RESTRICT` strategy (behaviorally the same as PostgreSQL's actual unwritten default, `NO ACTION`, for a plain non-deferred DELETE) prevents the deletion of a parent row (a user) if there are any referencing child rows (posts). This forces the application or a database administrator to explicitly handle the associated posts (e.g., re-assign them, archive them, or delete them manually) before the user can be deleted, aligning with the requirement for explicit review and action. `ON DELETE CASCADE` would automatically delete all posts, violating the requirement. `ON DELETE SET NULL` would make the `author_id` unknown, but the posts would still exist without explicit moderator action. `ON DELETE SET DEFAULT` would automatically re-assign posts to a predefined default user, which bypasses the explicit review process.

Q: Consider a `users` table with a `PRIMARY KEY` on `id` and a `UNIQUE` constraint on the `email` column. During a data migration, an application bug causes several new user records to be inserted with `NULL` values for their `email` address, as the email was not yet available. Assuming no `NOT NULL` constraint is explicitly defined on the `email` column, what is the expected behavior regarding the `UNIQUE` constraint on `email` for these `NULL` entries in PostgreSQL?

B. All users with `NULL` emails will be inserted successfully because `NULL` values are considered distinct for `UNIQUE` constraints in PostgreSQL. — In PostgreSQL, `UNIQUE` constraints allow multiple `NULL` values in the constrained column. Each `NULL` is considered distinct from other `NULL`s, meaning that multiple rows can have a `NULL` value in a column with a `UNIQUE` constraint without violating it. This behavior is explicitly mentioned in the lesson: '`UNIQUE` = no duplicates, but NULLs are allowed (and multiple NULLs count as different values in PostgreSQL)'. Option A is incorrect because `NULL`s are treated as distinct. Option C is incorrect because `UNIQUE` does not implicitly enforce `NOT NULL`; only `PRIMARY KEY` does. Option D is incorrect as PostgreSQL does not automatically modify `NULL` values in this manner.

Q: A critical `products` table in a high-traffic e-commerce system, containing millions of rows, was initially designed without a `NOT NULL` constraint on its `price` column. Due to recent data integrity issues, the engineering team decides to add this constraint to ensure all new and existing products have a defined price. The command `ALTER TABLE products ALTER COLUMN price SET NOT NULL;` is executed during peak hours. What is the most significant production impact or operational consideration during this operation?

B. The database will perform a full table scan to verify that all existing `price` values are non-null, potentially acquiring a strong lock and blocking read/write operations for a significant duration. — When adding a `NOT NULL` constraint to an existing column in PostgreSQL, the database must verify that all existing rows already satisfy this new constraint. For a table with millions of rows, this verification process involves a full table scan. This scan can be a long-running operation and acquires an `ACCESS EXCLUSIVE` lock for its duration — PostgreSQL's strictest lock mode, which blocks not just concurrent `INSERT`/`UPDATE`/`DELETE` but `SELECT` reads too, leading to significant performance degradation or outages during peak hours. Option A is incorrect because it's not just a metadata update; data verification is required. Option C is partially correct that it will fail if `NULL`s exist, but it's not a quick metadata change if a scan is needed. Option D is incorrect; PostgreSQL does not automatically convert `NULL` values to `0` when adding a `NOT NULL` constraint; it would instead raise an error if `NULL`s are found.

Who this module is for: You completed F-5 and can query data across multiple tables. Now you need to understand how to make the database enforce your rules automatically — so bad data cannot enter even if your application has a bug. Constraints are the database's immune system.

Why Constraints Matter

Imagine a bug in your application that accidentally submits an order with customer_id = 999 — but no customer with ID 999 exists. Without constraints, PostgreSQL happily stores this invalid order. Your data becomes inconsistent: you have orders that reference customers who do not exist.

Or your application has a bug that allows two users to register with the same email address. Now you have duplicate accounts and authentication breaks.

Constraints prevent these problems at the database level — not in your application code, not in your API layer, but at the very last line of defence before data is written to disk.

`PRIMARY KEY` — Every Row Needs a Unique Identity

A primary key uniquely identifies each row. No two rows can have the same primary key value, and it can never be NULL.

-- Using BIGSERIAL (auto-incrementing, recommended for most tables)
CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT      NOT NULL
);
 
-- Using UUID (when you need globally unique IDs)
CREATE TABLE sessions (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Composite primary key (rare — when no single column is unique, but a combination is)
CREATE TABLE order_items (
  order_id   BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)  -- each product appears once per order
);

When you insert a row that violates the primary key:

INSERT INTO users (id, email) VALUES (1, 'alice@example.com');  -- works
INSERT INTO users (id, email) VALUES (1, 'bob@example.com');    -- fails!
-- ERROR: duplicate key value violates unique constraint "users_pkey"
-- DETAIL: Key (id)=(1) already exists.

BIGSERIAL vs manual ID: when you use BIGSERIAL, PostgreSQL automatically assigns the next available integer. You never specify id in your INSERT — it is generated for you. This is the correct pattern for most tables.

`NOT NULL` — Mandatory Fields

NOT NULL prevents a column from ever being left empty.

CREATE TABLE products (
  id    BIGSERIAL PRIMARY KEY,
  name  TEXT      NOT NULL,   -- required
  price NUMERIC(10,2) NOT NULL, -- required
  sku   TEXT                  -- optional (can be NULL)
);
 
-- This fails:
INSERT INTO products (name) VALUES ('Keyboard');
-- ERROR: null value in column "price" of relation "products" violates not-null constraint
 
-- This works:
INSERT INTO products (name, price) VALUES ('Keyboard', 129.99);
-- sku is left as NULL — that is fine

Rule: if a column must always have a value, add NOT NULL. If the value is sometimes unknown or optional, leave the column nullable. Be intentional — a column without NOT NULL silently accepts missing data.

`UNIQUE` — No Duplicates Allowed

UNIQUE ensures no two rows have the same value in that column (or combination of columns).

-- Single column unique
CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT      NOT NULL UNIQUE  -- no two users can have the same email
);
 
-- Unique constraint on multiple columns (combination must be unique)
CREATE TABLE team_memberships (
  user_id BIGINT NOT NULL,
  team_id BIGINT NOT NULL,
  UNIQUE (user_id, team_id)  -- a user can only be in the same team once
);

When a unique constraint is violated:

INSERT INTO users (email) VALUES ('alice@example.com');  -- works
INSERT INTO users (email) VALUES ('alice@example.com');  -- fails!
-- ERROR: duplicate key value violates unique constraint "users_email_key"
-- DETAIL: Key (email)=(alice@example.com) already exists.

UNIQUE vs PRIMARY KEY:

PRIMARY KEY = unique + not null + the table's main identifier (one per table)
UNIQUE = no duplicates, but NULLs are allowed (and multiple NULLs count as different values in PostgreSQL)

`FOREIGN KEY` — Referential Integrity

A foreign key ensures that a value in one table references a real row in another table. It is the constraint that makes joins meaningful.

CREATE TABLE customers (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT      NOT NULL
);
 
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    NOT NULL REFERENCES customers(id),
  total       NUMERIC(10,2) NOT NULL
);
 
-- This fails because customer_id 999 does not exist:
INSERT INTO orders (customer_id, total) VALUES (999, 49.99);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- DETAIL: Key (customer_id)=(999) is not present in table "customers".

What happens when you delete a referenced row?

By default, trying to delete a customer who has orders fails:

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- DETAIL: Key (id)=(1) is still referenced from table "orders".

You control this behaviour with ON DELETE:

-- Option 1: Cascade — delete the customer's orders too
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    REFERENCES customers(id) ON DELETE CASCADE,
  total       NUMERIC(10,2) NOT NULL
);
-- Deleting a customer also deletes all their orders
 
-- Option 2: Set NULL — set customer_id to NULL when customer is deleted
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    REFERENCES customers(id) ON DELETE SET NULL,
  total       NUMERIC(10,2) NOT NULL
);
-- Order is kept but customer_id becomes NULL (only works if column is nullable)
 
-- Option 3: Restrict — refuse to delete if referenced rows exist. Note: if you
-- omit ON DELETE entirely, PostgreSQL's actual default is NO ACTION, not
-- RESTRICT — a distinct option that behaves the same for a plain DELETE but
-- (unlike RESTRICT) can be deferred to end-of-transaction if the constraint
-- is declared DEFERRABLE. Write RESTRICT explicitly if you want that behavior
-- guaranteed and non-deferrable.
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    REFERENCES customers(id) ON DELETE RESTRICT,
  total       NUMERIC(10,2) NOT NULL
);
 
-- Option 4: Set default value
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    DEFAULT 0 REFERENCES customers(id) ON DELETE SET DEFAULT,
  total       NUMERIC(10,2) NOT NULL
);

Which to use:

ON DELETE CASCADE — when child rows have no meaning without the parent (order items without an order)
ON DELETE SET NULL — when the relationship is optional and child rows are still useful alone (posts by a deleted user still exist, author is just unknown)
ON DELETE RESTRICT (PostgreSQL's actual unwritten default is the closely related NO ACTION) — when you want explicit control and prefer to handle deletion in your application

`CHECK` — Custom Business Rules

CHECK enforces any condition you can express as a boolean SQL expression:

CREATE TABLE products (
  id       BIGSERIAL PRIMARY KEY,
  name     TEXT      NOT NULL,
  price    NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  discount NUMERIC(5,2) CHECK (discount BETWEEN 0 AND 100),
  status   TEXT      NOT NULL CHECK (status IN ('active', 'inactive', 'archived'))
);
 
-- This fails:
INSERT INTO products (name, price, status) VALUES ('Keyboard', -10, 'active');
-- ERROR: new row for relation "products" violates check constraint "products_price_check"
-- DETAIL: Failing row contains (1, Keyboard, -10.00, null, active).
 
-- Multi-column check (defined at table level, not column level)
CREATE TABLE bookings (
  id         BIGSERIAL PRIMARY KEY,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CHECK (end_date > start_date)  -- end must be after start
);

`DEFAULT` — Automatic Values

DEFAULT specifies the value to use when a column is omitted from an INSERT:

CREATE TABLE posts (
  id         BIGSERIAL   PRIMARY KEY,
  title      TEXT        NOT NULL,
  published  BOOLEAN     NOT NULL DEFAULT false,
  view_count INTEGER     NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- All defaults apply:
INSERT INTO posts (title) VALUES ('My First Post');
 
-- Result:
-- id=1, title='My First Post', published=false, view_count=0,
-- created_at=NOW(), updated_at=NOW()

Adding Constraints to Existing Tables

You will often need to add constraints after a table already exists — because you are adding a rule to a live system:

-- Add a NOT NULL constraint (requires all existing rows to already be non-null)
ALTER TABLE products ALTER COLUMN price SET NOT NULL;
 
-- Add a UNIQUE constraint
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
 
-- Add a FOREIGN KEY constraint
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT;
 
-- Add a CHECK constraint
ALTER TABLE products
ADD CONSTRAINT products_price_positive CHECK (price >= 0);
 
-- Drop a constraint by name
ALTER TABLE products DROP CONSTRAINT products_price_positive;

Viewing constraint names

PostgreSQL auto-generates constraint names if you don't provide one. Find them with:

\d products    -- shows constraints in psql
 
-- Or query the catalog:
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass;

Practical Exercise: A Constrained Blog Schema

Build this schema from scratch, applying appropriate constraints at each step:

CREATE TABLE authors (
  id         BIGSERIAL    PRIMARY KEY,
  username   TEXT         NOT NULL UNIQUE,
  email      TEXT         NOT NULL UNIQUE,
  bio        TEXT,                          -- optional
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
 
CREATE TABLE posts (
  id          BIGSERIAL    PRIMARY KEY,
  author_id   BIGINT       NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
  title       TEXT         NOT NULL,
  slug        TEXT         NOT NULL UNIQUE,  -- URL-friendly version of title
  content     TEXT         NOT NULL,
  status      TEXT         NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft', 'published', 'archived')),
  published_at TIMESTAMPTZ,                  -- NULL until published
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
 
CREATE TABLE comments (
  id         BIGSERIAL    PRIMARY KEY,
  post_id    BIGINT       NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  author_id  BIGINT       REFERENCES authors(id) ON DELETE SET NULL,
  -- author_id can be NULL for anonymous comments
  content    TEXT         NOT NULL CHECK (LENGTH(content) >= 1),
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
 
CREATE TABLE tags (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT      NOT NULL UNIQUE
);
 
CREATE TABLE post_tags (
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)  -- a post can have each tag only once
);

Test your constraints:

-- Insert an author
INSERT INTO authors (username, email) VALUES ('alice', 'alice@example.com');
 
-- Insert a post
INSERT INTO posts (author_id, title, slug, content)
VALUES (1, 'Hello World', 'hello-world', 'My first post!');
 
-- Try to insert a post with an invalid status:
INSERT INTO posts (author_id, title, slug, content, status)
VALUES (1, 'Draft Post', 'draft-post', 'Content...', 'pending');
-- ERROR: violates check constraint "posts_status_check"
 
-- Try to insert a duplicate slug:
INSERT INTO posts (author_id, title, slug, content)
VALUES (1, 'Another Post', 'hello-world', 'Different content');
-- ERROR: violates unique constraint "posts_slug_key"
 
-- Delete the author — their post is deleted too (CASCADE)
DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;  -- empty — cascaded delete removed it

Summary

Constraint	What it enforces
`PRIMARY KEY`	Unique + non-null identifier for each row
`NOT NULL`	Column must always have a value
`UNIQUE`	No two rows can have the same value (NULLs are distinct)
`FOREIGN KEY`	Value must reference an existing row in another table
`ON DELETE CASCADE`	Delete child rows when parent is deleted
`ON DELETE SET NULL`	Set FK to NULL when parent is deleted
`ON DELETE RESTRICT`	Block deletion if child rows exist (unwritten default is the closely related `NO ACTION`)
`CHECK`	Any boolean expression must be true
`DEFAULT`	Value to use when column is omitted from INSERT

The philosophy: the database is the last line of defence. Enforce every rule you can at the database level — it is faster, safer, and catches bugs that application-level validation misses.

One gotcha with FOREIGN KEY specifically: PostgreSQL does not automatically create an index on the FK column itself (only on the primary/unique key it points at). If you're deleting or updating rows on the parent table frequently, an unindexed FK column on the child table forces a sequential scan to check for referencing rows — add an explicit index on it if that's your access pattern.

Module F-7 completes Phase 1 — building your first real application schema, connecting from application code, and the basic tools every engineer needs before moving to Phase 2.

Next: F-7 — Your First Real Application Schema →

Knowledge Check

A software team is designing a database schema for a social media platform. They have users and posts tables, where posts.author_id is a foreign key referencing users.id. The product requirement states that when a user account is deleted, their posts should not be automatically removed, but instead, the posts should be explicitly reviewed by a moderator for potential re-assignment to an 'anonymous' user or archival. Which ON DELETE strategy for the posts.author_id foreign key best supports this architectural requirement?

Consider a users table with a PRIMARY KEY on id and a UNIQUE constraint on the email column. During a data migration, an application bug causes several new user records to be inserted with NULL values for their email address, as the email was not yet available. Assuming no NOT NULL constraint is explicitly defined on the email column, what is the expected behavior regarding the UNIQUE constraint on email for these NULL entries in PostgreSQL?

A critical products table in a high-traffic e-commerce system, containing millions of rows, was initially designed without a NOT NULL constraint on its price column. Due to recent data integrity issues, the engineering team decides to add this constraint to ensure all new and existing products have a defined price. The command ALTER TABLE products ALTER COLUMN price SET NOT NULL; is executed during peak hours. What is the most significant production impact or operational consideration during this operation?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule F-5: Connecting Tables — Joins Demystified Next Module F-7: Your First Real Application Schema

Discussion

Join the discussion

Loading comments...