Constraints: Rules the Database Enforces

A constraint is a rule you write into CREATE TABLE, and the engine checks it on every INSERT and UPDATE. Once it is in the schema, no query and no application can put a row that breaks it into the table. By the end you can declare the everyday constraints and predict which rows a schema will refuse.

Five rules you can declare

A room-booking table with four of them:

CREATE TABLE rooms (
    code     TEXT PRIMARY KEY,
    capacity INTEGER NOT NULL CHECK (capacity > 0),
    floor    INTEGER NOT NULL DEFAULT 1
);

• PRIMARY KEY identifies the row: no duplicates, no NULLs, one per table. The engine also keeps an index on it -- a sorted lookup structure, so finding a room by code never scans the table.
• NOT NULL: the column must always hold a value. An INSERT writing NULL there, or leaving the column out with nothing to fall back on, is refused.
• UNIQUE: no two rows may share the value. Same duplicate check as a primary key, but you may declare one for every natural key the table has (email, username, slug), and it may still be NULL.
• CHECK (condition): any condition that must be true of the row. capacity > 0 outlaws a zero-seat room; it may name several columns and combine them with AND and OR, like a WHERE.
• DEFAULT value: used when an INSERT does not mention that column at all.

Two rows go in; the second names no floor:

INSERT INTO rooms (code, capacity, floor) VALUES ('B12', 40, 2);
INSERT INTO rooms (code, capacity) VALUES ('C01', 8);

B12|40|2
C01|8|1

C01 took floor 1 from the DEFAULT. Without it that column would have come out NULL -- and since it is also NOT NULL, the insert would have been rejected.

What a violation looks like

Each of these breaks one rule, and SQLite says which:

INSERT INTO rooms (code, capacity) VALUES ('B12', 12);
INSERT INTO rooms (code, capacity) VALUES ('D04', 0);
INSERT INTO rooms (code, capacity) VALUES ('E09', NULL);

Error: near line 11: UNIQUE constraint failed: rooms.code
Error: near line 12: CHECK constraint failed: rooms
Error: near line 13: NOT NULL constraint failed: rooms.capacity

The row is not stored, and the line number is your script's. A CHECK names only the table; the other two name the column too.

INSERT OR IGNORE

Sometimes you want the bad row dropped rather than the script stopped. INSERT OR IGNORE skips a row that violates a constraint and carries on:

INSERT OR IGNORE INTO rooms (code, capacity) VALUES ('B12', 12);
INSERT OR IGNORE INTO rooms (code, capacity) VALUES ('D04', 0);
INSERT OR IGNORE INTO rooms (code, capacity) VALUES ('F22', 15);

Then the table holds:

B12|40|2
C01|8|1
F22|15|1

No error, and only the legal row arrived. Which rows survive is a readout of what the schema enforces -- which is how your exercise is graded.

FOREIGN KEY

CREATE TABLE bookings (id INTEGER PRIMARY KEY, room TEXT REFERENCES rooms(code));

Every value in bookings.room must now exist as a code in rooms. That guarantee has a name: referential integrity -- no booking can point at a room nobody created. ON DELETE CASCADE removes a room's bookings with the room; the default, NO ACTION, refuses to delete a room that still has bookings. SQLite checks foreign keys only after the connection runs PRAGMA foreign_keys = ON; -- a pragma is a per-connection setting, not part of the schema.

Common mistakes

• Requiring the role but not defaulting it. Ada's own insert names no role, so it dies with NOT NULL constraint failed: users.role; with Ada gone, two probe rows that should have collided with her get in instead.
• Writing the lower age bound as age > 0. Zero is a legal age here, so the 7|[email protected]|0|member line is thrown away and your output is a line short.
• Writing the upper bound as age <= 150. The probe row aged 150 is accepted and an extra 6|[email protected]|150|member line appears.
• Leaving UNIQUE off the email column. The probe row reusing an address already taken is stored, and an extra id-3 line turns up mid-output.

Your exercise

Replace the ruleless users table in the editor with one that enforces the membership rules, add the two real members, and report what survived. The probe rows underneath are the test -- leave them exactly as written, since each is aimed at one rule. The most expensive slip is requiring a role without also defaulting it.