Lesson 16 of 20

In progress

Course progress

SQL

Constraints that protect the data

Use primary keys, NOT NULL, UNIQUE, CHECK and foreign keys so bad rows are rejected at write time instead of found in a report.

A constraint is a rule the database enforces on every write. It is the cheapest bug prevention available, because it fails at the moment the bad data arrives rather than in a report six weeks later.

What you will learn

  • Apply the five constraints that cover most needs.
  • Understand what a foreign key does on delete.
  • Name constraints so errors are readable.

The table

SQL
Editable. Runs in your browser.





Press Run to see the output.

The second insert fails, because a due date before the borrow date is not a loan anyone can reason about. The database refuses it, and the application does not have to remember to check.

Primary key

id INTEGER PRIMARY KEY guarantees uniqueness and non-null. It also gives every row an address, which is what foreign keys point at and what makes updates precise.

A composite primary key is possible when the natural identity is a pair, such as (member_id, book_id) in a join table. Use it for link tables, and a surrogate id for entities that will be referenced elsewhere.

NOT NULL

NOT NULL means the column must have a value on every insert. It is the difference between "we always set this" and "we think we always set this".

Add it at creation time when you can. Adding it later to a column with NULLs requires filling those rows first, which is a migration of its own.

UNIQUE

SQL
Editable. Runs in your browser.





Press Run to see the output.

A duplicate email now fails. That is usually the intent for a login identifier, and it is what stops two accounts for one person.

Most databases allow multiple NULLs in a unique column, because each NULL is unknown and cannot conflict. If a column is optional and must still be unique when present, that behaviour is convenient; if not, add NOT NULL.

A unique constraint across several columns enforces a combination: UNIQUE (member_id, book_id, borrowed_on) prevents the same loan being recorded twice.

CHECK

CHECK evaluates a boolean expression on each row. It handles ranges, allowed values and relationships between columns.

SQL
Editable. Runs in your browser.





Press Run to see the output.

Keep checks simple and absolute. A rule that depends on today’s date or on another table belongs in application logic or a trigger, because a CHECK is evaluated only when the row changes.

Foreign keys

A foreign key ties a row to another table:

SQL
Editable. Runs in your browser.





Press Run to see the output.

The second insert fails because member 999 does not exist. The first succeeds.

ON DELETE decides what happens when the parent goes:

  • CASCADE deletes the children. Right for a member’s holds.
  • RESTRICT refuses the delete while children exist. Right for a copy that has loan history.
  • SET NULL keeps the child and clears the reference. Right for optional relationships.

SQLite requires PRAGMA foreign_keys = ON per connection. It is off by default for backward compatibility, which means a schema full of foreign keys can enforce nothing until you enable it. Other databases enforce them always.

Naming constraints

SQL
Editable. Runs in your browser.





Press Run to see the output.

Naming a constraint makes the error message specific and the migration that drops it possible. UNIQUE constraint failed: tags.label is readable; constraint failed is not, and a name makes it unambiguous.

Constraints versus validation

Application validation gives good messages and can check things a database cannot, such as "this email is not already pending". Database constraints are the last line of defence and the only one that holds when a script, an import or a second service writes to the same table.

Use both. Treat the database as the authority on what is legal, and the application as the place that explains why.

Adding a foreign key and assuming it is enforced. In SQLite it is inert until PRAGMA foreign_keys = ON, so orphan rows accumulate quietly and only fail when someone joins them.

Create a reviews table with a rating from 1 to 5, a required body, a unique combination of member and book, and a foreign key to members that deletes reviews with the member. Insert one valid row and one invalid rating.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

The second insert fails on the rating check, and a duplicate member/book pair would fail on the unique constraint. The trim(body) check rejects whitespace-only text, which passes a plain NOT NULL.

Recap

  • Primary keys identify, NOT NULL requires, UNIQUE forbids duplicates, CHECK bounds values.
  • Foreign keys prevent orphans; ON DELETE chooses between cascade, restrict and null.
  • Enable SQLite’s foreign keys explicitly and name constraints for readable errors.

Next, indexes: what makes a lookup fast and what it costs.

Can I add a constraint to an existing table?
SQLite cannot add most constraints in place. Rebuild the table: create the new shape, copy, drop, rename.
Are constraints slow?
They add a check per write and an index lookup for unique and foreign keys. That cost is small next to the bugs they prevent.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.