Constraints and data integrity

A constraint is a write-time promise. It tells every insert, update, and delete what the table will refuse, so invalid data cannot become tomorrow's production incident.

Constraints reject invalid writes

The previous lesson used constraints as part of table design. This lesson treats them as the product: each rule has a job, a failure mode, and a cost if you omit it. Start by reading a table as a contract. name is required. sku is unique. price_cents cannot be negative. is_active starts as published-ready unless the insert says otherwise.

Run the valid inserts first. Then try a write that should fail: a duplicate SKU, a missing name, or a negative price. The error is the feature. It is the database refusing to store a fact that would break later reports, payments, or joins.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  sku TEXT NOT NULL UNIQUE,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  is_active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO products (product_id, name, sku, price_cents)
VALUES
  (1, 'SQL notebook', 'NOTE-SQL-001', 1499),
  (2, 'Database sticker', 'STICKER-DB-001', 299);

SELECT product_id, name, sku, price_cents, is_active
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

NOT NULL rejects missing values, not empty ones

NOT NULL means “this fact must exist.” It does not mean “this text must be useful.” An empty string is still a value. In the example below, author 2 is stored even though display_name is blank. A form that submitted an empty field did not violate NOT NULL.

Decide what “required” really means. If the business needs a visible name, reject blank and whitespace-only text with a CHECK. If a middle name may be unknown, leave that column nullable instead of storing a fake empty string.

CREATE TABLE authors (
  author_id INTEGER PRIMARY KEY,
  display_name TEXT NOT NULL
);

INSERT INTO authors (author_id, display_name)
VALUES
  (1, 'Amina Idrissi'),
  (2, '');

SELECT author_id, display_name, length(display_name) AS name_length
FROM authors
ORDER BY author_id;

Edit the query, predict the rows it will return, then run it.

CREATE TABLE authors (
  author_id INTEGER PRIMARY KEY,
  display_name TEXT NOT NULL CHECK (length(trim(display_name)) > 0)
);

INSERT INTO authors (author_id, display_name)
VALUES (1, 'Amina Idrissi');

SELECT author_id, display_name
FROM authors;

Edit the query, predict the rows it will return, then run it.

UNIQUE protects identity that people rely on

A unique constraint says that a value, or a combination of values, can identify at most one row. Learner email addresses are a common example: two accounts must not share the same login. The primary key still identifies the row internally; UNIQUE protects a business identifier that people use.

CREATE TABLE learners (
  learner_id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

INSERT INTO learners (learner_id, full_name, email)
VALUES
  (101, 'Amina Idrissi', 'amina@example.com'),
  (102, 'Bilal Karim', 'bilal@example.com');

SELECT learner_id, full_name, email
FROM learners
ORDER BY learner_id;

Edit the query, predict the rows it will return, then run it.

Uniqueness has a NULL caveat. In SQLite, UNIQUE does not treat two NULLs as equal, so several rows can omit an optional SKU. That can be correct for a draft product with no code yet. It is incorrect if “missing” should still be unique, or if two drafts should not collide later when a code is assigned.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  optional_sku TEXT UNIQUE
);

INSERT INTO products (product_id, name, optional_sku)
VALUES
  (1, 'SQL notebook', 'NOTE-SQL-001'),
  (2, 'Draft workbook', NULL),
  (3, 'Workshop handout', NULL);

SELECT product_id, name, optional_sku
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

Composite UNIQUE matches a local business rule

Some uniqueness is not global. Seat A1 can exist in two different events. It must not exist twice in the same event. UNIQUE (event_id, seat_code) states that local rule without making seat_code unique by itself.

The primary key still identifies one ticket everywhere. The composite unique constraint is a second promise: within one event, a seat can be sold once. Mixing those two jobs into one column is how schemas become brittle.

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  event_id INTEGER NOT NULL,
  seat_code TEXT NOT NULL,
  UNIQUE (event_id, seat_code)
);

INSERT INTO tickets (ticket_id, event_id, seat_code)
VALUES
  (1, 10, 'A1'),
  (2, 10, 'A2'),
  (3, 20, 'A1');

SELECT event_id, seat_code
FROM tickets
ORDER BY event_id, seat_code;

Edit the query, predict the rows it will return, then run it.

CHECK states a condition, not a type

Types reject the wrong kind of value. CHECK rejects the wrong meaning: a negative price, a status outside an allowed list, a quantity of zero. Name important checks so error messages and later migrations stay readable.

Write the condition as a sentence first. “Price is never negative.” “Status is one of draft, published, or archived.” Then translate it. A check that encodes a temporary UI label, such as CHECK (status <> 'oops'), is not a real business rule.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  status TEXT NOT NULL,
  CONSTRAINT price_non_negative CHECK (price_cents >= 0),
  CONSTRAINT status_allowed CHECK (status IN ('draft', 'published', 'archived'))
);

INSERT INTO products (product_id, name, price_cents, status)
VALUES
  (1, 'SQL notebook', 1499, 'published'),
  (2, 'Schema poster', 899, 'draft');

SELECT product_id, name, price_cents, status
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

DEFAULT fills an omitted column, not an explicit NULL

A default is a starting value for writers who have nothing better to say. Lesson 1 is unpublished because the insert omitted is_published. Lesson 2 is published because the insert supplied 1. The default did not override the explicit value.

Inserting NULL is not the same as omitting the column. If the column is nullable, an explicit NULL stores NULL. If it is NOT NULL without a default that can apply, the write fails. Defaults also do not repair existing rows when you add a column later; that is a migration question.

CREATE TABLE lessons (
  lesson_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  is_published INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO lessons (lesson_id, title)
VALUES (1, 'Constraints and data integrity');

INSERT INTO lessons (lesson_id, title, is_published)
VALUES (2, 'CREATE TABLE and schema design', 1);

SELECT lesson_id, title, is_published, created_at
FROM lessons
ORDER BY lesson_id;

Edit the query, predict the rows it will return, then run it.

Foreign keys protect relationships at write time

A foreign key says the child value must refer to a living parent row. Here every ticket belongs to an event. In SQLite that protection is not automatic: this connection must run PRAGMA foreign_keys = ON. Without it, the FOREIGN KEY clause is stored, but orphan tickets can still be inserted.

PRAGMA foreign_keys = ON;

CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  event_id INTEGER NOT NULL,
  seat_code TEXT NOT NULL,
  FOREIGN KEY (event_id) REFERENCES events(event_id),
  UNIQUE (event_id, seat_code)
);

INSERT INTO events (event_id, title) VALUES (10, 'SQL workshop');
INSERT INTO tickets (ticket_id, event_id, seat_code)
VALUES
  (1, 10, 'A1'),
  (2, 10, 'A2');

SELECT event.title, ticket.seat_code
FROM events AS event
JOIN tickets AS ticket ON ticket.event_id = event.event_id
ORDER BY ticket.seat_code;

Edit the query, predict the rows it will return, then run it.

Deleting a parent is a separate product decision. The default behavior, once foreign keys are on, is to reject a delete that would orphan children. That is usually the safe starting point for tickets, orders, and enrollments. ON DELETE CASCADE removes children with the parent. ON DELETE SET NULL keeps the child and clears the reference, which requires a nullable foreign key.

PRAGMA foreign_keys = ON;

CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  event_id INTEGER NOT NULL,
  seat_code TEXT NOT NULL,
  FOREIGN KEY (event_id) REFERENCES events(event_id)
);

INSERT INTO events (event_id, title) VALUES (10, 'SQL workshop');
INSERT INTO tickets (ticket_id, event_id, seat_code) VALUES (1, 10, 'A1');

SELECT event_id, title FROM events;
SELECT ticket_id, event_id, seat_code FROM tickets;

Edit the query, predict the rows it will return, then run it.

The database is the last shared boundary

Keep friendly validation in the application: required-field messages, disabled buttons, preview screens. Keep integrity in the database: the rules that remain true if a developer bypasses the UI. Those layers answer different questions. One is “help the person.” The other is “do not store a lie.”

• If a rule protects money, inventory, identity, or a relationship, encode it as a constraint.
• If a rule is only a display preference, keep it in the application.
• If two writers must never disagree, the table—not a single code path—owns the rule.

Review constraints before you ship a table

1. Write the one-row meaning, then list the facts that must always be true.
2. Mark only genuinely required facts NOT NULL.
3. Add UNIQUE for identifiers people rely on, including composite rules.
4. Add CHECK for ranges, enumerations, and non-blank text.
5. Add DEFAULT only for a real starting value.
6. Add foreign keys, enable them in SQLite, and choose delete behavior deliberately.
7. Insert a valid example, then attempt the illegal writes you care about.

Independent lab: protect a workshop registration schema

One row in workshops is one scheduled workshop. One row in registrations is one person holding seats in one workshop. The provided solution uses required values, allowed statuses, positive counts, a foreign key, and a composite unique rule so the same email cannot register twice for the same workshop.

Read the constraints before running them. Then try the illegal writes: a zero-capacity workshop, a second registration for Amina in workshop 1, and a registration for workshop_id 99.

PRAGMA foreign_keys = ON;

-- One row in workshops is one scheduled workshop.
CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  capacity INTEGER NOT NULL CHECK (capacity > 0),
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'full', 'cancelled'))
);

-- One row in registrations is one person holding one seat in one workshop.
CREATE TABLE registrations (
  registration_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL,
  attendee_email TEXT NOT NULL,
  seats INTEGER NOT NULL CHECK (seats > 0),
  status TEXT NOT NULL DEFAULT 'confirmed' CHECK (status IN ('confirmed', 'waitlist', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (workshop_id) REFERENCES workshops(workshop_id),
  UNIQUE (workshop_id, attendee_email)
);

INSERT INTO workshops (workshop_id, title, capacity, status)
VALUES
  (1, 'SQL foundations', 12, 'open'),
  (2, 'Schema design', 8, 'open');
INSERT INTO registrations (registration_id, workshop_id, attendee_email, seats, status)
VALUES
  (101, 1, 'amina@example.com', 1, 'confirmed'),
  (102, 1, 'bilal@example.com', 2, 'waitlist'),
  (103, 2, 'amina@example.com', 1, 'confirmed');

SELECT workshop.title, registration.attendee_email, registration.seats, registration.status
FROM registrations AS registration
JOIN workshops AS workshop ON workshop.workshop_id = registration.workshop_id
ORDER BY workshop.workshop_id, registration.registration_id;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Treating an empty string as “missing” while also using NOT NULL.
• Assuming UNIQUE rejects multiple NULLs in SQLite.
• Making a value globally unique when the rule is unique only inside a parent row.
• Relying on application validation for money, identity, or relationships.
• Declaring a foreign key in SQLite without enabling foreign keys on the connection.
• Choosing ON DELETE CASCADE because it is convenient rather than because the child has no independent meaning.
• Adding a default and expecting it to rewrite existing rows.

Lesson review

• I can explain why integrity rules belong in the table, not only in the UI.
• I can distinguish NOT NULL, empty strings, and NULL.
• I can choose column-level and composite UNIQUE rules.
• I can write a CHECK that matches a real business condition.
• I can predict when DEFAULT applies.
• I can enable and use a foreign key, then choose delete behavior deliberately.