Skip to content
Ameen Abozaid
Englishالعربية
PublishedBEG-051-L04

Constraints that protect data

  • Lesson 4 of 6
  • 10 min read
  • Beginner
On This Lesson 11 topics

Lesson Overview

So far the library database is an empty room with the right person holding the key. Now we put rules on the door. A constraint is a rule written into the table definition that the database checks on every INSERT and UPDATE. If a row breaks the rule, the database refuses it and tells you which rule was broken.

You met NOT NULL, primary keys, and REFERENCES in BEG-049 and BEG-050. This lesson adds UNIQUE and CHECK, puts all four kinds side by side on the Library tables, and, most importantly, makes you deliberately break each rule and read the error. Reading these errors fluently is a real backend skill.

Why this matters: application code checks data too, but code has bugs, and several programs may write to the same database. A constraint is the last line of defense, and it is the one that cannot be forgotten or bypassed by a different program.

This lesson builds on Lesson 3 (the library database owned by library_app) and on BEG-049 and BEG-050 (keys, CREATE TABLE, INSERT).

Learning Objectives

By the end of this lesson you will be able to:

  • Add NOT NULL, UNIQUE, CHECK, and REFERENCES rules to a CREATE TABLE statement.
  • Predict which constraint rejects a given bad row.
  • Read a PostgreSQL constraint error and name the table, the rule, and the offending value.
  • Explain how NULL interacts with UNIQUE and CHECK.
  • Name a constraint and add one to an existing table with ALTER TABLE.
  • Explain why database constraints are needed even when the application validates input.

Written Explanation

The four rules

The PostgreSQL manual groups these under "Constraints". Here they are in library terms:

ConstraintDoor ruleExample
NOT NULLA value is requiredA book must have a title
UNIQUENo two rows share this valueNo two books have the same ISBN
CHECK (condition)The condition must holdCopies cannot be negative
REFERENCES other (col)The value must exist in the other tableA loan must point to a real member

A primary key combines UNIQUE and NOT NULL, according to the manual. A foreign key is the REFERENCES rule: the value must match a row in the referenced table.

A table with all four

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

Read each line as a rule: isbn is required and unique; title is required; author_id is required and must exist in authors; copies is required and at least zero. \d books lists them all under "Check constraints", "Indexes", and "Foreign-key constraints".

Breaking each rule on purpose

With a good author and ISBN already in the tables, each of these inserts breaks exactly one rule. The messages are real output from PostgreSQL 18.3.

sql
INSERT INTO books (id, isbn, title, author_id, copies)
VALUES (3, '978-0-00-000003-3', NULL, 1, 1);
text
ERROR:  null value in column "title" of relation "books" violates not-null constraint
DETAIL:  Failing row contains (3, 978-0-00-000003-3, null, 1, 1).

A repeated ISBN:

text
ERROR:  duplicate key value violates unique constraint "books_isbn_key"
DETAIL:  Key (isbn)=(978-0-00-000001-1) already exists.

A negative number of copies:

text
ERROR:  new row for relation "books" violates check constraint "books_copies_check"
DETAIL:  Failing row contains (3, 978-0-00-000003-3, Neg, 1, -1).

A loan for a member that does not exist:

text
ERROR:  insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey"
DETAIL:  Key (member_id)=(999) is not present in table "members".

Every message has the same two parts. The first line names the kind of rule and (usually) its name. DETAIL shows the value that caused it. When PostgreSQL generates a name itself, the pattern in these examples is table, column, and a suffix (_key, _check, _fkey); the manual does not promise that pattern, so do not depend on it in code.

Foreign keys protect both ways

A foreign key also stops you from removing a row that others still point at:

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

By default (NO ACTION in the manual) the delete is refused, because a loan would be left pointing at nobody. Other behaviors, such as deleting the loans too (CASCADE), exist but are a deliberate choice for later. Tables with foreign keys also affect DROP TABLE: you cannot drop books while loans still references it, unless you drop loans first or add CASCADE. Drop in the reverse order you created, which Lesson 6 uses.

NULL has special rules

NULL means "no value", and it behaves differently from other values:

  • UNIQUE allows several NULLs, because the manual says that by default NULLs are treated as distinct. The same is true when you test it: two rows with NULL in a unique column are accepted. Add NOT NULL when a value must exist and be unique, as with isbn.
  • CHECK passes when the condition is NULL. CHECK (copies >= 0) does not reject a NULL copies value; the manual says a check is satisfied when its expression is true or null. So pair CHECK with NOT NULL when a value is required.
  • A foreign key column that is NULL is not checked by default. Declaring it NOT NULL, as loans.member_id is, closes that gap.

This is why the Library table writes NOT NULL CHECK (...), not just CHECK (...).

Naming constraints and adding them later

You can give any constraint your own name, which makes error messages clearer:

sql
ALTER TABLE books
  ADD CONSTRAINT copies_not_negative CHECK (copies >= 0);

Adding a constraint to a table that already holds rows checks the existing rows first. If a row already breaks the rule, the statement fails. In our test, a table with qty = -3 produced check constraint "qty_not_negative" of relation "t_demo" is violated by some row. You must fix the data first (for example with an UPDATE ... WHERE), then add the rule.

Examples

Example 1: one rule, one rejection (simple)

sql
CREATE TABLE members (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);
INSERT INTO members VALUES (1, 'Sara', 'sara@example.com');
INSERT INTO members VALUES (2, 'Dup',  'sara@example.com');

The second insert fails with duplicate key value violates unique constraint "members_email_key". The row is not inserted, and nothing else changes. The first row is untouched.

Example 2: a failed insert leaves the table as it was (practical)

sql
SELECT count(*) FROM members;    -- 3
INSERT INTO members VALUES (1, 'Dup', 'd@example.com');
SELECT count(*) FROM members;    -- still 3

The second statement fails because id = 1 already exists (members_pkey). A rejected statement is cancelled as a whole: it never partly succeeds. For a multi-row INSERT, one bad row means none are inserted. Always read the error and the row count that follows a statement.

Example 3: the application had a bug, the database caught it (realistic)

A web form validates "copies must be 0 or more", but a second tool (a nightly import script written by someone else) forgets the check and sends -5. Because the table has CHECK (copies >= 0), the import fails with violates check constraint "books_copies_check" for that row. Without the constraint, the library would show minus five copies and every report afterwards would be wrong, with no clue where the bad value came from. Validation in code improves the user experience (friendly messages, early feedback); the constraint guarantees the data. You need both, and neither replaces the other.

Backend Insight

In Java services, a database constraint violation arrives as an exception from the database driver, and a mature service translates it into a sensible response: a duplicate email becomes "that email is already registered" rather than a crash. That only works if the constraint exists, with a name you can recognize. Naming important constraints (CONSTRAINT members_email_unique UNIQUE (email)) lets the code and the logs identify the rule. Later modules show the translation; for now, practice reading the error text.

Constraints also document the model. Anyone reading \d books immediately learns the business rules that the application assumes. And they let you trust data when analyzing it: if copies can never be negative, reports need no defensive code.

Common Mistakes

Mistake 1: Constraints only in application code

  • Incorrect: "The form checks the email, so the table does not need UNIQUE."
  • Correct: put the rule in the table, and optionally check in code for friendlier messages. Two programs, a race between two users, or a bug can all bypass code.

Mistake 2: Using CHECK without NOT NULL

A NULL passes CHECK (copies >= 0). If the value is required, say so with NOT NULL.

Mistake 3: Thinking UNIQUE stops duplicate NULLs

By default it does not. A unique column that may be NULL can hold many empty values.

Mistake 4: Adding a constraint to dirty data and not reading the error

ALTER TABLE ... ADD CONSTRAINT fails if existing rows break it. Query for the bad rows (SELECT ... WHERE copies < 0), repair them, then add the constraint.

Exercises

Try each exercise yourself before opening the solutions.

Level A: Foundation

A1. Which constraint would you use to make sure no two members share an email address?

Show solution A1

A1. UNIQUE (ideally together with NOT NULL).

A2. Which constraint stops price from being negative?

Show solution A2

A2. CHECK (price >= 0) (or > 0 if free items are not allowed).

A3. What does REFERENCES authors (id) on books.author_id stop?

Show solution A3

A3. It stops a book from pointing at an author id that does not exist in authors. It also stops deleting an author that books still reference.

Level B: Application

B1. Write a CREATE TABLE for rooms: id primary key, name required and unique, seats required and at least 1.

Show solution B1

B1.

sql
CREATE TABLE rooms (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL UNIQUE,
  seats INTEGER NOT NULL CHECK (seats >= 1)
);

B2. For your rooms table, write three INSERT statements, one for each broken rule (NOT NULL, UNIQUE, CHECK), and say which constraint kind each error message will mention.

Show solution B2

B2. INSERT INTO rooms VALUES (1, NULL, 10); gives a not-null violation. After INSERT INTO rooms VALUES (1, 'A', 10);, the statement INSERT INTO rooms VALUES (2, 'A', 10); gives a unique violation (it mentions a unique constraint, probably named rooms_name_key). INSERT INTO rooms VALUES (3, 'B', 0); gives a check violation (rooms_seats_check).

B3. The row INSERT INTO rooms VALUES (5, 'Hall', NULL); is accepted when seats is declared INTEGER CHECK (seats >= 1) without NOT NULL. Explain why, and how to fix it.

Show solution B3

B3. A CHECK is satisfied when its expression is true or NULL, and NULL >= 1 is NULL. Fix: declare seats INTEGER NOT NULL CHECK (seats >= 1).

Level C: Challenge

C1. A table staff has email TEXT UNIQUE (no NOT NULL). Two rows are inserted with NULL email, and both succeed. Explain why, and give two fixes depending on whether email is required.

Show solution C1

C1. By default UNIQUE treats NULLs as distinct, so they never collide. If email is required, add NOT NULL so no NULL can be stored. If it is optional but unique when present, the default already does that; for "at most one NULL" you would need NULLS NOT DISTINCT, which the manual documents for newer versions.

C2. You must add CHECK (copies >= 0) to a populated books table, but the command fails. Describe the exact steps to get the rule in without deleting the table.

Show solution C2

C2. Find the bad rows with SELECT * FROM books WHERE copies < 0;, correct them with UPDATE books SET copies = 0 WHERE copies < 0; (after deciding that is the right repair), repeat the SELECT to confirm nothing is left, then run ALTER TABLE books ADD CONSTRAINT copies_not_negative CHECK (copies >= 0);.

Mini Challenge

Take the members/books/loans tables and write a "break log": a table with four rows, one per rule kind. For each, record the exact INSERT or DELETE you ran, the first line of the error, and the DETAIL line. Add a fifth row for a rule you add yourself, such as CHECK (length(title) > 0) on books.title, and break that too.

Check your work: every error is copied from a real run, each shows which rule fired, and the failed statements left the row counts unchanged.

Summary & Next Lesson

Key takeaways

  • NOT NULL, UNIQUE, CHECK, and foreign keys are checked by the database on every INSERT and UPDATE.
  • A rejected statement changes nothing; the error names the rule and DETAIL shows the value.
  • UNIQUE allows multiple NULLs, and CHECK passes NULL; combine with NOT NULL when a value is required.
  • Foreign keys also block deleting a row that others reference, and affect drop order.
  • Constraints in the database protect data even when application code has a bug or a different program writes to it.

Next lesson

The next lesson, "First look at transactions", shows how to group several statements so they succeed or fail together, using BEGIN, COMMIT, and ROLLBACK.

Self-check

  1. Which constraint prevents duplicate ISBNs?
  2. Does CHECK (copies >= 0) reject a NULL?
  3. What happens to a multi-row INSERT if one row breaks a rule?
  4. Why can you not drop books while loans references it?
  5. Why keep constraints in the database when the application already validates?
Show answers
  1. UNIQUE (with NOT NULL).
  2. No. A NULL makes the condition null, which a check accepts; add NOT NULL.
  3. The statement fails as a whole and none of its rows are inserted.
  4. The foreign key in loans depends on books; drop loans first or use CASCADE.
  5. Code can have bugs and other programs can write to the database; the constraint always applies.

References

  • PostgreSQL documentation, Constraints. Supports: check, not-null, unique (NULLs distinct by default, NULLS NOT DISTINCT), primary key, foreign key and its ON DELETE actions (NO ACTION default), naming constraints, and that a check passes on a null result.
  • All error messages and the "violated by some row" message were produced on a local PostgreSQL 18.3 test server. The manual does not specify the pattern of generated constraint names, so the _key, _check, _fkey pattern is described only as what this version produced.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson

Type to search levels, phases, modules and lessons.