Lesson 10 of 10

Table constraints

16 min
6 checks
3 hints
Go to task

Goal

Rules the database enforces

Explanation

Constraints are rules the database enforces on its own: PRIMARY KEY gives every row a unique identifier, NOT NULL stops a value from being left empty, UNIQUE stops a value from repeating, CHECK enforces a condition on the value, and REFERENCES creates a foreign key, which stops you from pointing to a row that doesn't exist.

Validating data in your application code alone isn't enough, because a migration script, another service or a manual edit can get around that validation. A constraint, on the other hand, rejects any INSERT that breaks it, wherever it comes from.

Here's something about SQLite that many people forget: foreign keys are only enforced once you run PRAGMA foreign_keys = ON; That setting applies to each connection separately, and without it SQLite lets you write REFERENCES in the table definition but doesn't enforce it.

Your task

Turn on foreign keys, then create a gift card table called cards with these columns: id as the primary key, a required customer_id that references customers(id), code as required text that can't repeat, and balance as a required integer that can't go below zero.

Checks

6 checks

The cards table has its four columns
The id column is the primary key
The other three columns are required
A duplicate code is rejected
A negative balance is rejected
A card for a customer that doesn't exist is rejected

Tables in this lesson

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

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       INTEGER NOT NULL,
  placed_on   TEXT    NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Layan',  'Riyadh'),
  (2, 'Khalid', 'Jeddah'),
  (3, 'Reem',   'Dammam'),
  (4, 'Fahd',   'Riyadh');

INSERT INTO orders (customer_id, total, placed_on) VALUES
  (1, 120, '2026-09-01'),
  (1,  80, '2026-09-12'),
  (2, 300, '2026-09-03'),
  (3,  45, '2026-09-20'),
  (2,  60, '2026-09-25');

Table constraints

Explanation

Your task

Checks

Hints