SQL Constraints

Protect your data with NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY and FOREIGN KEY constraints.

Lesson 11 of 102Intermediate17 min read

A database is only useful if its data can be trusted. An order for a customer who doesn't exist, two accounts with the same email, a negative quantity or a product with no name: each of these is a small bug waiting to cause a big problem later, in a report, an invoice or a delivery.

Constraints are rules attached to columns or tables that the database checks every time data is inserted or changed. If a change would break a rule, the database refuses it with an error, and the bad data never gets in. That's far more reliable than hoping every piece of application code remembers to check.

In this lesson you'll add the six most important constraints to your tables, see the errors they produce, and learn how foreign keys keep related tables consistent with each other, which is the "relational" part of a relational database.

NOT NULL, UNIQUE and DEFAULT

  • NOT NULL means the column must always have a value: name TEXT NOT NULL.
  • UNIQUE means no two rows may share the same value: email VARCHAR(255) UNIQUE. It's perfect for usernames, emails and product codes.
  • DEFAULT supplies a value when an INSERT leaves the column out: created_at DATE DEFAULT CURRENT_DATE or active BOOLEAN DEFAULT TRUE.

A UNIQUE column can usually contain several NULLs, because NULL means "unknown" rather than an actual value. Combine NOT NULL UNIQUE when every row must have a different, real value.

CHECK

A CHECK constraint tests a condition on each row:

SQL
quantity INTEGER NOT NULL CHECK (quantity > 0),
rating   INTEGER CHECK (rating BETWEEN 1 AND 5)

Any INSERT or UPDATE that makes the condition false is rejected. CHECK can also compare two columns, as a table-level constraint: CHECK (end_date >= start_date). MySQL only enforces CHECK constraints from version 8.0.16; older versions silently ignore them.

PRIMARY KEY

A primary key uniquely identifies each row. It's automatically NOT NULL and UNIQUE, and a table can have only one. It's usually a single id column, but it can combine columns, called a composite key:

SQL
CREATE TABLE enrolments (
    student_id INTEGER,
    course_id  INTEGER,
    PRIMARY KEY (student_id, course_id)
);

Here a student can enrol in many courses, and a course has many students, but the same student can't enrol in the same course twice.

FOREIGN KEY and referential integrity

A foreign key says that a column's values must match a primary key in another table:

SQL
customer_id INTEGER NOT NULL REFERENCES customers(id)

Now the database refuses an order for customer 99 if there's no customer 99, a guarantee called referential integrity. It also decides what happens when the referenced row is deleted:

OptionWhen a customer is deleted
ON DELETE RESTRICT (default)The delete is refused while orders exist
ON DELETE CASCADETheir orders are deleted too
ON DELETE SET NULLTheir orders keep existing with customer_id set to NULL

SQLite only enforces foreign keys after you run PRAGMA foreign_keys = ON; in each connection. MySQL (with the InnoDB engine) and PostgreSQL enforce them automatically.

SQL Constraints Syntax

Constraint syntax

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    code        VARCHAR(20) UNIQUE,
    quantity    INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
    CONSTRAINT valid_code CHECK (LENGTH(code) >= 4)
);

SQL Constraints Summary

  • Constraints are rules the database checks on every insert and update.
  • NOT NULL requires a value; UNIQUE forbids duplicates; DEFAULT fills in missing values.
  • CHECK (condition) rejects rows that break a rule.
  • A PRIMARY KEY is unique and not null; it can span several columns.
  • A FOREIGN KEY ... REFERENCES keeps related tables consistent; ON DELETE controls deletes.

SQL Constraints Examples

Each example shows the code, the output it produces and what each part does.

Example 1

A well-protected table

SQL
PRAGMA foreign_keys = ON;

CREATE TABLE reviews (
    id          INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL REFERENCES products(id),
    email       VARCHAR(255) NOT NULL,
    rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    created_at  DATE DEFAULT CURRENT_DATE,
    UNIQUE (product_id, email)
);

INSERT INTO reviews (product_id, email, rating)
VALUES (3, 'ana@example.com', 5);
Output
1 row inserted.
Line-by-line explanation
  1. Line 1SQLite needs this setting to enforce foreign keys.
  2. Line 5Reviews must belong to a product that exists.
  3. Line 7Ratings outside 1 to 5 are rejected.
  4. Line 8The date is filled in automatically.
  5. Line 9A table-level UNIQUE: each email can review each product only once.
Example 2

Constraints rejecting bad data

SQL
INSERT INTO reviews (product_id, email, rating) VALUES (3, 'ben@example.com', 7);
INSERT INTO reviews (product_id, email, rating) VALUES (99, 'ben@example.com', 4);
INSERT INTO reviews (product_id, email, rating) VALUES (3, 'ana@example.com', 4);
INSERT INTO reviews (product_id, rating) VALUES (3, 4);
Output
Error: CHECK constraint failed: rating BETWEEN 1 AND 5
Error: FOREIGN KEY constraint failed
Error: UNIQUE constraint failed: reviews.product_id, reviews.email
Error: NOT NULL constraint failed: reviews.email
Line-by-line explanation
  1. Line 1A rating of 7 breaks the CHECK constraint.
  2. Line 2There's no product 99.
  3. Line 3Ana has already reviewed product 3.
  4. Line 4Every review needs an email. Exact error wording differs between databases.
Example 3

ON DELETE CASCADE

SQL
CREATE TABLE wishlists (
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    product_id  INTEGER NOT NULL REFERENCES products(id),
    PRIMARY KEY (customer_id, product_id)
);

INSERT INTO wishlists VALUES (5, 3), (5, 6), (2, 6);

DELETE FROM customers WHERE id = 5;

SELECT * FROM wishlists;
Output
customer_id | product_id
------------+-----------
2           | 6
Line-by-line explanation
  1. Line 2When a customer is deleted, their wishlist rows go too.
  2. Line 4A composite primary key: each product appears once per customer's list.
  3. Line 9Deleting Ema (customer 5) removes her two wishlist rows automatically.

Common SQL Constraints Mistakes

Relying only on application code

Don't
-- "the app always checks the rating"
rating INTEGER
Do
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5)

Imports, scripts and other apps can all write to the same table. Only the database sees every change.

Forgetting PRAGMA foreign_keys in SQLite

Don't
INSERT INTO orders (customer_id, product_id, order_date)
VALUES (99, 1, '2024-08-01');   -- accepted!
Do
PRAGMA foreign_keys = ON;
INSERT INTO orders (customer_id, product_id, order_date)
VALUES (99, 1, '2024-08-01');   -- FOREIGN KEY constraint failed

Using CASCADE everywhere

Don't
customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE   -- on invoices
Do
customer_id INTEGER REFERENCES customers(id) ON DELETE RESTRICT

Deleting a customer shouldn't silently delete financial records. Choose CASCADE only for data that truly belongs to the parent, like wishlist items.

SQL Constraints in Real Projects

Constraints in production databases

Banks, shops and hospitals rely on constraints so that an account can't have two owners with the same ID number, an order can't point at a product that was never created, and a dose can't be negative. When an application has a bug, the constraint error is often the first sign, caught before the bad data spreads.

SQL Constraints FAQ

What is the difference between a primary key and a unique constraint?

Both prevent duplicate values. A table has only one primary key, and its columns can never be NULL; it's the main identifier other tables point to. A table can have several unique constraints, such as on email and on username, and in most databases their columns can contain NULL.

Can I add a constraint to an existing table?

Yes, with ALTER TABLE ... ADD CONSTRAINT in PostgreSQL, MySQL and SQL Server, for example ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price >= 0);. The command fails if existing rows already break the rule, so clean the data first. SQLite supports only a few ALTER TABLE changes and usually needs the table to be rebuilt.

Do constraints slow down inserts and updates?

A little, because the database checks each rule and maintains the indexes behind primary keys and unique constraints. The cost is small compared with the cost of bad data: duplicate customers, negative stock or orders pointing to deleted products. Keep the constraints; they are the last line of defence.

Should foreign key columns have an index?

Usually yes. Joins and ON DELETE checks look up rows by the foreign key column, such as orders.customer_id. MySQL's InnoDB creates that index automatically, but PostgreSQL and SQLite don't, so add CREATE INDEX on foreign key columns you join or delete by.

Should I use a natural key or an id column as the primary key?

A natural key uses real data, such as an ISBN or a country code; a surrogate key is an artificial id. Surrogate ids never need to change, which makes them the common default, while natural keys can still get a UNIQUE constraint so duplicates are blocked. Use a natural key only when it's truly stable and unique.

SQL Constraints Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the column so every product must have a name.

name TEXT
Fill in the blanksmedium

Complete the column so customer_id must match a customer.

customer_id INTEGER  customers(id)
Multiple choicemedium

Which constraint stops two users signing up with the same email?

Predict the outputmedium

With ON DELETE RESTRICT, what happens when you delete a customer who has orders?

Multiple choicehard

Which is true about a primary key?

SQL Constraints Quiz

5 questions to check what you learned.

  1. What does a constraint do?

    • Enforces a rule on the data, rejecting changes that break it
    • Speeds up queries
    • Creates a backup
    • Sorts the table

  2. What does DEFAULT 1 do?

    • Forces the value to always be 1
    • Rejects values other than 1
    • Sets the first row to 1
    • Uses 1 when an INSERT doesn't give a value

  3. What is referential integrity?

    • Every table has an index
    • Every foreign key value matches an existing row in the referenced table
    • Every column has a name
    • Data is encrypted

  4. What must you run in SQLite before foreign keys are enforced?

    • ENABLE KEYS;
    • SET FOREIGN = TRUE;
    • PRAGMA foreign_keys = ON;
    • Nothing, they're always on

  5. What is a composite primary key?

    • A key made of text
    • Two separate primary keys
    • A foreign key that points to itself
    • A primary key made of more than one column

Related SQL topics

10CREATE & ALTER TABLEDesign your own tables: choose data types, create tables, change them with ALTER TABLE and remove them safely.12JOINCombine rows from related tables with INNER JOIN and LEFT JOIN, using keys and table aliases.08INSERT, UPDATE, DELETEAdd new rows, change existing data and remove rows safely, without losing anything by accident.
Esc
↑↓ to navigateEnter to open