Skip to editor content
learningsql.orglesson 21 of 25

Constraints And Keys

Practice exercise →

A database that lets you store anything anywhere is not very useful in practice. What stops someone from inserting an order for a customer that does not exist? What prevents two users from sharing the same email address? What keeps a product price from being set to a negative number?

The answer is constraints. Constraints are rules you attach to a table or column that the database engine enforces automatically, before any data reaches disk. If an insert or update would violate a constraint, the database rejects it with an error. This means you get data integrity for free — you do not have to write application-layer checks for every rule you care about.

This lesson covers the five constraints you will use most often: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK. It also covers DEFAULT values, which are not strictly constraints but work alongside them.

Primary Keys

Every table should have a primary key — one column (or combination of columns) whose value uniquely identifies each row. The database enforces two things automatically: the value must be unique across all rows, and it must never be NULL.

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT    NOT NULL,
  price    DECIMAL(10, 2) NOT NULL
);

INSERT INTO products VALUES (1, 'Keyboard', 79.99);
INSERT INTO products VALUES (2, 'Mouse', 29.99);
INSERT INTO products VALUES (3, 'Monitor', 349.00);

SELECT * FROM products;

Try adding a duplicate id — the database will refuse it:

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products VALUES (1, 'Keyboard', 79.99);
INSERT INTO products VALUES (1, 'Mouse', 29.99);

You will see an error like UNIQUE constraint failed: products.id. The second insert never lands.

In practice, most databases support auto-incrementing primary keys so you never have to supply the value manually. In SQLite the syntax is INTEGER PRIMARY KEY (which implicitly auto-increments). In PostgreSQL you would use SERIAL or GENERATED ALWAYS AS IDENTITY.

Foreign Keys

A foreign key links a column in one table to the primary key of another table. This enforces referential integrity — you cannot reference a row that does not exist, and (depending on configuration) you cannot delete a row that is still being referenced.

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total       DECIMAL(10, 2) NOT NULL,
  placed_at   TEXT NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice Chen', 'alice@example.com');
INSERT INTO customers VALUES (2, 'Bob Patel', 'bob@example.com');

INSERT INTO orders VALUES (101, 1, 149.99, '2024-01-15');
INSERT INTO orders VALUES (102, 2, 59.50,  '2024-01-16');
INSERT INTO orders VALUES (103, 1, 220.00, '2024-01-17');

SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Notice the REFERENCES customers(id) clause on customer_id. If you try to insert an order with customer_id = 99 and no customer with that id exists, the database rejects it.

ON DELETE Behavior

You can control what happens to child rows when a parent row is deleted:

  • ON DELETE CASCADE — deleting the parent automatically deletes all child rows
  • ON DELETE SET NULL — child rows have the foreign key column set to NULL
  • ON DELETE RESTRICT — deletion is blocked if any child rows exist (default behavior)
CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id            INTEGER PRIMARY KEY,
  name          TEXT NOT NULL,
  department_id INTEGER REFERENCES departments(id) ON DELETE SET NULL
);

INSERT INTO departments VALUES (10, 'Engineering');
INSERT INTO departments VALUES (20, 'Marketing');

INSERT INTO employees VALUES (1, 'Diana', 10);
INSERT INTO employees VALUES (2, 'Marco', 10);
INSERT INTO employees VALUES (3, 'Sara',  20);

DELETE FROM departments WHERE id = 10;

SELECT * FROM employees;

After deleting the Engineering department, Diana and Marco still exist but their department_id is now NULL.

Unique Constraints

A UNIQUE constraint ensures no two rows share the same value in a column (or combination of columns). Unlike a primary key, a unique column can contain NULL — and most databases treat each NULL as distinct, so multiple NULL values are allowed.

CREATE TABLE users (
  id       INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email    TEXT NOT NULL UNIQUE
);

INSERT INTO users VALUES (1, 'alice',  'alice@example.com');
INSERT INTO users VALUES (2, 'bob',    'bob@example.com');

SELECT * FROM users;

Try inserting a duplicate username to see the constraint fire:

CREATE TABLE users (
  id       INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email    TEXT NOT NULL UNIQUE
);

INSERT INTO users VALUES (1, 'alice', 'alice@example.com');
INSERT INTO users VALUES (2, 'alice', 'other@example.com');

You can also create composite unique constraints — for example, ensuring a user can only review each product once:

CREATE TABLE reviews (
  user_id    INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  rating     INTEGER NOT NULL,
  UNIQUE (user_id, product_id)
);

NOT NULL

By default, any column can store NULL unless you explicitly prevent it. NOT NULL makes a column required — every insert and update must supply a real value.

This is important because NULL has unusual behavior in SQL: it is not equal to anything, including itself. Forgetting a NOT NULL constraint on a column you intend to always have a value leads to confusing query results and extra defensive code.

CHECK Constraints

CHECK lets you write an arbitrary condition that every row must satisfy. If the condition evaluates to false, the insert or update is rejected.

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  price    DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  stock    INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category TEXT NOT NULL CHECK (category IN ('electronics', 'clothing', 'food'))
);

INSERT INTO products VALUES (1, 'Laptop', 999.00, 5, 'electronics');
INSERT INTO products VALUES (2, 'T-Shirt', 24.99, 50, 'clothing');

SELECT * FROM products;

Now try inserting a product with a negative price or an invalid category — both will be rejected:

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  price    DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  category TEXT NOT NULL CHECK (category IN ('electronics', 'clothing', 'food'))
);

INSERT INTO products VALUES (1, 'Gadget', -5.00, 'electronics');

DEFAULT Values

DEFAULT is not a constraint in the strict sense, but it works alongside them. When an insert omits a column, the database uses the default value instead of NULL.

CREATE TABLE articles (
  id         INTEGER PRIMARY KEY,
  title      TEXT NOT NULL,
  published  INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (date('now'))
);

INSERT INTO articles (id, title) VALUES (1, 'Getting Started with SQL');
INSERT INTO articles (id, title, published) VALUES (2, 'Advanced Joins', 1);

SELECT * FROM articles;

The published flag defaults to 0 (draft), and created_at defaults to today's date — both without the caller having to supply them.

Try It Yourself

Design a small schema for a library system. Books belong to one author, members can borrow books, and each borrow record tracks dates and a status. Apply appropriate constraints throughout.

CREATE TABLE authors (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id        INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  isbn      TEXT NOT NULL UNIQUE,
  copies    INTEGER NOT NULL DEFAULT 1 CHECK (copies >= 0)
);

CREATE TABLE members (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE borrows (
  id          INTEGER PRIMARY KEY,
  book_id     INTEGER NOT NULL REFERENCES books(id),
  member_id   INTEGER NOT NULL REFERENCES members(id),
  borrowed_on TEXT NOT NULL DEFAULT (date('now')),
  due_on      TEXT NOT NULL,
  returned_on TEXT,
  status      TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'returned', 'overdue'))
);

INSERT INTO authors VALUES (1, 'J.R.R. Tolkien');
INSERT INTO authors VALUES (2, 'Frank Herbert');

INSERT INTO books VALUES (1, 'The Fellowship of the Ring', 1, '978-0547928210', 3);
INSERT INTO books VALUES (2, 'Dune', 2, '978-0441013593', 2);

INSERT INTO members VALUES (1, 'Emma Wilson', 'emma@library.org');
INSERT INTO members VALUES (2, 'Luis Torres', 'luis@library.org');

INSERT INTO borrows (id, book_id, member_id, borrowed_on, due_on)
VALUES (1, 1, 1, '2024-03-01', '2024-03-15');

INSERT INTO borrows (id, book_id, member_id, borrowed_on, due_on)
VALUES (2, 2, 2, '2024-03-02', '2024-03-16');

SELECT b.title, m.name, br.borrowed_on, br.due_on, br.status
FROM borrows br
JOIN books b   ON b.id  = br.book_id
JOIN members m ON m.id  = br.member_id;

Experiment: try inserting a borrow with an invalid status, or delete an author who has books. Watch how the constraints respond.

Key Takeaways

  • PRIMARY KEY uniquely identifies each row and implicitly enforces NOT NULL and UNIQUE
  • FOREIGN KEY links rows across tables and prevents orphaned references — use ON DELETE CASCADE or ON DELETE SET NULL to control what happens when parent rows are removed
  • UNIQUE prevents duplicate values in a column or combination of columns, but allows multiple NULL values
  • NOT NULL forces a column to always have a value — use it on every column that should never be absent
  • CHECK enforces arbitrary business rules at the database level, such as valid ranges or allowed enum values
  • DEFAULT provides a fallback value when a column is omitted from an insert, reducing boilerplate in application code
  • Constraints defined at the database level protect data integrity regardless of which application, script, or tool writes to the database

Pro Tip: Treat the database as the last line of defense, not the first. Your application should validate input before it ever reaches SQL — but constraints ensure that even a buggy migration script, a raw psql session, or a future integration cannot silently corrupt your data. Define constraints early and liberally; it is far easier to relax a constraint later than to clean up a table full of invalid rows.

Next Steps

With constraints securing your data, the next lesson introduces recursive queries — a technique for traversing hierarchical data like org charts, category trees, and threaded comments using a single SQL statement.

Next lesson

Recursive Queries

Learn recursive SQL queries with WITH RECURSIVE CTEs to traverse hierarchical data, generate sequences, and solve self-referencing problems.

25 min