Integrity constraints and foreign keys in practice

An integrity constraint is a rule about the data that the database itself checks on every insert, update and delete; a change that would break the rule is refused with an error, and the data stays as it was. SQL has five everyday constraints: NOT NULL (a value must be given), UNIQUE (no two rows share a value), CHECK (each row must satisfy a condition), PRIMARY KEY (unique and never NULL) and FOREIGN KEY (a value must match a key of another table). Declaring a rule in the schema, rather than in the program that writes the data, means that every program obeys it, including the import script someone writes next year. This lesson runs each constraint in SQLite, compares the referential actions a foreign key can take, shows how deferred checking allows circular references, and covers the places where SQLite and PostgreSQL 18 differ, up to PostgreSQL’s new constraints against overlapping time ranges.

Kinds of constraint, and why they belong in the schema

The constraints of a relational database fall into four families:

• Domain constraints: column types, NOT NULL, CHECK and domains. They refuse a value outside the allowed set for its column.
• Key constraints: PRIMARY KEY and UNIQUE. They refuse a second row with the same key value.
• Referential constraints: FOREIGN KEY. They refuse a reference to a row that does not exist.
• Rules across rows: exclusion and temporal constraints, triggers and assertions. They refuse combinations of rows, such as two overlapping bookings of one room.

Codd’s 1985 articles name two rules that apply to every relational database. Entity integrity: no component of a primary key may be null. Referential integrity: for each distinct non-null foreign key value there must exist a matching primary key value from the same domain. His rule 10, integrity independence, adds the reason to declare everything else in the database too: constraints should be defined in the database language and “storable in the catalog, not in the application programs”. A check in application code protects only the paths that go through that code.

NOT NULL, UNIQUE and CHECK at work

Here is a student table with a constraint on every column, and seven rows that test them:

CREATE TABLE student (
  roll_no   INTEGER PRIMARY KEY,
  name      TEXT    NOT NULL,
  email     TEXT    UNIQUE,
  programme TEXT    NOT NULL CHECK (programme IN ('BTech', 'MSc', 'PhD')),
  score     REAL    CHECK (score BETWEEN 0 AND 100)
);
INSERT INTO student VALUES (101, 'Asha',   'asha@uni.example',  'BTech', 81.5);
INSERT INTO student VALUES (102, NULL,     'bilal@uni.example', 'MSc',   74.0);  -- no name
INSERT INTO student VALUES (103, 'Chitra', 'asha@uni.example',  'PhD',   90.0);  -- Asha's email
INSERT INTO student VALUES (104, 'Dev',    'dev@uni.example',   'MBA',   65.0);  -- not a programme
INSERT INTO student VALUES (105, 'Esha',   'esha@uni.example',  'BTech', 104.0); -- score too high
INSERT INTO student VALUES (106, 'Farah',  NULL,                'MSc',   NULL);  -- no email, no score yet
INSERT INTO student VALUES (107, 'Gopal',  NULL,                'PhD',   NULL);  -- a second missing email

SELECT roll_no, name, quote(email) AS email, programme, quote(score) AS score FROM student;

┌─────────┬───────┬────────────────────┬───────────┬───────┐
│ roll_no │ name  │       email        │ programme │ score │
├─────────┼───────┼────────────────────┼───────────┼───────┤
│ 101     │ Asha  │ 'asha@uni.example' │ BTech     │ 81.5  │
│ 106     │ Farah │ NULL               │ MSc       │ NULL  │
│ 107     │ Gopal │ NULL               │ PhD       │ NULL  │
└─────────┴───────┴────────────────────┴───────────┴───────┘

Runtime error near line 9: NOT NULL constraint failed: student.name
Runtime error near line 10: UNIQUE constraint failed: student.email
Runtime error near line 11: CHECK constraint failed: programme IN ('BTech', 'MSc', 'PhD')
Runtime error near line 12: CHECK constraint failed: score BETWEEN 0 AND 100

Each refused row was refused by a different constraint, and SQLite names it in the message: NOT NULL on the name, UNIQUE on the email, and the two CHECK conditions on the programme and the score. Two results surprise people:

• Two students without an email were both accepted. For a UNIQUE constraint, SQLite’s documentation says, NULL values are “distinct from all other values, including other NULLs”, and PostgreSQL behaves the same by default. Since PostgreSQL 15, UNIQUE NULLS NOT DISTINCT makes it treat NULLs as equal, so that only one row may lack the value. When a value must be present and unique, declare it NOT NULL UNIQUE.
• A missing score passed CHECK (score BETWEEN 0 AND 100). A CHECK fails only when its condition is false; for NULL the condition is unknown, and both engines document that true or unknown passes. Add NOT NULL when the value is required.

A CHECK sees one row at a time. PostgreSQL’s documentation rules out subqueries in a CHECK expression and lets it read only the columns of the current row; SQLite does not allow subqueries in them either, so “the total of all payments may not exceed the invoice” needs a trigger or application code. A CHECK with an IN list, as on the programme column, is the closest thing to the domains of the relational model. PostgreSQL also has named domains, a type with constraints of its own that many columns can share, for example CREATE DOMAIN percentage AS numeric CHECK (VALUE BETWEEN 0 AND 100). Its documentation advises keeping NOT NULL off the domain and putting it on the columns that need it.

Foreign keys and referential actions

A foreign key is checked from both ends. Inserting or updating a child row checks that the parent exists. Deleting a parent row, or changing its key, raises a question: what happens to the rows that refer to it? The foreign key’s referential action answers it, separately ON DELETE and ON UPDATE:

• NO ACTION, the default: the change is refused if references are left dangling when the constraint is checked, at the end of the statement or, for a deferred constraint, at commit.
• RESTRICT: the change is refused at once if any row refers to the parent.
• CASCADE: the referring rows are deleted too, or updated to the new key value.
• SET NULL: the referring columns become NULL.
• SET DEFAULT: the referring columns get their default value, which must itself match a parent row unless it is NULL.

The choice follows the meaning of the data. Rooms exist only as parts of a hostel, so they go with it. Residents remain students when their hostel closes, so their reference is cleared. A scheduled warden duty means that the hostel is still in use, so the delete is refused:

PRAGMA foreign_keys = ON;
CREATE TABLE hostel (hostel_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE room (
  room_id   INTEGER PRIMARY KEY,
  hostel_id INTEGER NOT NULL REFERENCES hostel ON DELETE CASCADE      -- rooms go with their hostel
);
CREATE TABLE resident (
  roll_no   INTEGER PRIMARY KEY,
  hostel_id INTEGER REFERENCES hostel ON DELETE SET NULL             -- residents stay, unassigned
);
CREATE TABLE warden_duty (
  duty_id   INTEGER PRIMARY KEY,
  hostel_id INTEGER NOT NULL REFERENCES hostel ON DELETE RESTRICT     -- duties block the delete
);
INSERT INTO hostel VALUES (1, 'North'), (2, 'South');
INSERT INTO room VALUES (11, 1), (12, 1), (21, 2);
INSERT INTO resident VALUES (101, 1), (102, 2);
INSERT INTO warden_duty VALUES (900, 2);

DELETE FROM hostel WHERE hostel_id = 1;   -- cascades to rooms, sets residents' hostel to NULL
DELETE FROM hostel WHERE hostel_id = 2;   -- refused: a warden duty still refers to it

SELECT 'hostels' AS t, count(*) AS rows_left FROM hostel
UNION ALL SELECT 'rooms', count(*) FROM room
UNION ALL SELECT 'residents', count(*) FROM resident;
SELECT roll_no, quote(hostel_id) AS hostel_id FROM resident ORDER BY roll_no;

┌───────────┬───────────┐
│     t     │ rows_left │
├───────────┼───────────┤
│ hostels   │ 1         │
│ rooms     │ 1         │
│ residents │ 2         │
└───────────┴───────────┘
┌─────────┬───────────┐
│ roll_no │ hostel_id │
├─────────┼───────────┤
│ 101     │ NULL      │
│ 102     │ 2         │
└─────────┴───────────┘

Runtime error near line 21: FOREIGN KEY constraint failed

Deleting the North hostel removed its two rooms and set resident 101’s hostel to NULL. Deleting the South hostel was refused because a warden duty still refers to it, and nothing about it changed. SQLite reports every foreign key violation with the same short message, FOREIGN KEY constraint failed; PostgreSQL’s messages name the constraint and the key value.

NO ACTION and RESTRICT both refuse to leave a dangling reference, so the difference only shows inside a transaction. PostgreSQL’s documentation puts it in one sentence: unlike NO ACTION, RESTRICT “does not allow the check to be deferred”. SQLite’s documentation says the same: even on a deferred foreign key, RESTRICT “causes SQLite to return an error immediately”:

PRAGMA foreign_keys = ON;
CREATE TABLE parent (id INTEGER PRIMARY KEY);
CREATE TABLE child_no_action (
  parent_id INTEGER REFERENCES parent (id) ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE child_restrict (
  parent_id INTEGER REFERENCES parent (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED);
INSERT INTO parent VALUES (1), (2);
INSERT INTO child_no_action VALUES (1);
INSERT INTO child_restrict VALUES (2);

-- NO ACTION: the check waits until COMMIT, so the transaction may repair the reference first.
BEGIN;
DELETE FROM parent WHERE id = 1;
UPDATE child_no_action SET parent_id = NULL;
COMMIT;

-- RESTRICT: the delete is refused at once, even though the constraint is deferrable.
BEGIN;
DELETE FROM parent WHERE id = 2;
UPDATE child_restrict SET parent_id = NULL;
COMMIT;

SELECT id AS parents_left FROM parent;

┌──────────────┐
│ parents_left │
├──────────────┤
│ 2            │
└──────────────┘

Runtime error near line 19: FOREIGN KEY constraint failed

With NO ACTION, the transaction deleted parent 1, repaired its child before committing, and the commit succeeded. With RESTRICT, the delete of parent 2 failed on the spot, so the parent is still there. Choose RESTRICT when no transaction should ever be allowed to remove a parent that is in use, and NO ACTION when a transaction may delete and repair in either order.

Two more details matter in practice. A composite foreign key whose columns are partly NULL is not checked at all under the default matching rule; PostgreSQL’s MATCH FULL refuses such half-filled references, while SQLite treats every foreign key as MATCH SIMPLE. And PostgreSQL does not create an index on the referring columns, although every delete of a parent must find its children; its documentation suggests indexing them, which is usually right for tables you join or delete from often.

SQLite: foreign keys are off until you switch them on

SQLite parsed REFERENCES clauses long before it enforced them, and by the time enforcement arrived, in version 3.6.19, a great many databases already declared foreign keys, some of them incorrectly. To keep those databases working, SQLite leaves enforcement off on every new connection:

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer TEXT NOT NULL);
CREATE TABLE order_line (
  order_id INTEGER NOT NULL REFERENCES orders (order_id),
  item     TEXT    NOT NULL
);
INSERT INTO orders VALUES (1, 'Asha');

-- Foreign keys are off on a new SQLite connection: this orphan line is accepted.
PRAGMA foreign_keys;
INSERT INTO order_line VALUES (7, 'pen');

-- The pragma does nothing inside a transaction...
BEGIN;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
COMMIT;

-- ...and works outside one. New orphans are refused from now on, old ones stay.
PRAGMA foreign_keys = ON;
INSERT INTO order_line VALUES (8, 'ink');
INSERT INTO order_line VALUES (1, 'notebook');
PRAGMA foreign_key_check;
SELECT order_id, item FROM order_line;

┌──────────────┐
│ foreign_keys │
├──────────────┤
│ 0            │
└──────────────┘
┌──────────────┐
│ foreign_keys │
├──────────────┤
│ 0            │
└──────────────┘
┌────────────┬───────┬────────┬──────┐
│   table    │ rowid │ parent │ fkid │
├────────────┼───────┼────────┼──────┤
│ order_line │ 1     │ orders │ 0    │
└────────────┴───────┴────────┴──────┘
┌──────────┬──────────┐
│ order_id │   item   │
├──────────┼──────────┤
│ 7        │ pen      │
│ 1        │ notebook │
└──────────┴──────────┘

Runtime error near line 20: FOREIGN KEY constraint failed

The first order line refers to order 7, which does not exist, and SQLite stored it. Switching enforcement on inside a transaction did nothing, as the documentation warns (“This pragma is a no-op within a transaction”). Outside the transaction the pragma took effect, and the next orphan was refused. The old orphan stayed: turning the check on does not look at existing rows. PRAGMA foreign_key_check does, and it lists the table, the rowid, the parent table and the number of the broken foreign key. PRAGMA integrity_check does not report foreign key errors at all.

The habit that follows is simple. Make the code that opens a connection run PRAGMA foreign_keys = ON every time, and run PRAGMA foreign_key_check in tests and after migrations, because a single connection without the pragma, in a script or an admin tool, is enough to let orphans in.

Deferred constraints and circular references

Every department has a manager, and every employee, the manager included, belongs to a department. Each table now has a NOT NULL foreign key to the other, so whichever row you insert first refers to a row that does not exist yet. Constraints are checked at the end of each statement by default, which makes the first insert fail. A deferred constraint is checked at COMMIT instead, so both rows can be inserted in one transaction:

PRAGMA foreign_keys = ON;
-- Every department has a manager, and every employee belongs to a department.
CREATE TABLE department (
  dept_id    INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  manager_id INTEGER NOT NULL REFERENCES employee (emp_id) DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE employee (
  emp_id  INTEGER PRIMARY KEY,
  name    TEXT    NOT NULL,
  dept_id INTEGER NOT NULL REFERENCES department (dept_id) DEFERRABLE INITIALLY DEFERRED
);

-- On its own, each statement is a transaction, and the first insert finds no manager 501.
INSERT INTO department VALUES (10, 'Chemistry', 501);

-- In one transaction, the deferred checks run at COMMIT, when both rows exist.
BEGIN;
INSERT INTO department VALUES (10, 'Chemistry', 501);
INSERT INTO employee VALUES (501, 'Meera', 10);
COMMIT;

-- A COMMIT that still breaks a deferred check fails, and in SQLite the transaction stays open.
BEGIN;
INSERT INTO department VALUES (11, 'Biology', 777);
COMMIT;
SELECT count(*) AS departments_seen_inside FROM department;
ROLLBACK;
SELECT d.name AS department, e.name AS manager
FROM department AS d JOIN employee AS e ON e.emp_id = d.manager_id;

┌─────────────────────────┐
│ departments_seen_inside │
├─────────────────────────┤
│ 2                       │
└─────────────────────────┘
┌────────────┬─────────┐
│ department │ manager │
├────────────┼─────────┤
│ Chemistry  │ Meera   │
└────────────┴─────────┘

Runtime error near line 15: FOREIGN KEY constraint failed
Runtime error near line 26: FOREIGN KEY constraint failed

Outside a transaction each statement commits on its own, so even a deferred constraint fails at once. Inside BEGIN and COMMIT, the department and its manager arrived one after the other, and the check at commit found both. The last transaction shows what happens when a deferred check still fails: the COMMIT is refused. In SQLite the transaction then stays open, which is why the count inside it still sees two departments, and only ROLLBACK ends it. PostgreSQL behaves differently: there, a COMMIT whose deferred check fails rolls the whole transaction back.

In SQLite only foreign keys can be deferred, with DEFERRABLE INITIALLY DEFERRED; the pragma defer_foreign_keys defers all of them for one transaction. PostgreSQL accepts DEFERRABLE on UNIQUE, PRIMARY KEY, EXCLUDE and foreign key constraints, but not on NOT NULL or CHECK, and SET CONSTRAINTS … DEFERRED or IMMEDIATE changes the timing inside a transaction. When deferral is not available, make one of the two references nullable, insert the department without a manager, and fill the manager in with an UPDATE once the employee exists.

Types: SQLite’s flexible columns and STRICT tables

SQLite’s documentation is frank that in an ordinary table “datatypes are advisory rather than mandatory”: a column declared INTEGER converts text that looks like a number and stores anything else as it is. Since version 3.37.0, a table declared STRICT refuses values that cannot be stored in the declared type without loss:

-- An ordinary SQLite table converts what it can and stores the rest as it is.
CREATE TABLE marks_flexible (roll_no INTEGER, score INTEGER);
INSERT INTO marks_flexible VALUES (101, '87'), (102, 'abc'), (103, 91.0), (104, 91.5);
SELECT roll_no, score, typeof(score) AS stored_as FROM marks_flexible;

-- A STRICT table accepts only values that fit the declared type without loss.
CREATE TABLE marks_strict (roll_no INTEGER, score INTEGER) STRICT;
INSERT INTO marks_strict VALUES (101, '87');
INSERT INTO marks_strict VALUES (102, 'abc');
INSERT INTO marks_strict VALUES (103, 91.0);
INSERT INTO marks_strict VALUES (104, 91.5);
SELECT roll_no, score, typeof(score) AS stored_as FROM marks_strict;

┌─────────┬───────┬───────────┐
│ roll_no │ score │ stored_as │
├─────────┼───────┼───────────┤
│ 101     │ 87    │ integer   │
│ 102     │ abc   │ text      │
│ 103     │ 91    │ integer   │
│ 104     │ 91.5  │ real      │
└─────────┴───────┴───────────┘
┌─────────┬───────┬───────────┐
│ roll_no │ score │ stored_as │
├─────────┼───────┼───────────┤
│ 101     │ 87    │ integer   │
│ 103     │ 91    │ integer   │
└─────────┴───────┴───────────┘

Runtime error near line 9: cannot store TEXT value in INTEGER column marks_strict.score
Runtime error near line 11: cannot store REAL value in INTEGER column marks_strict.score

The ordinary table converted '87' and 91.0 to integers, kept 'abc' as text and 91.5 as a real number, so the column now holds three types. The STRICT table converted the two values that fit and refused the other two. A STRICT table allows only the types INT, INTEGER, REAL, TEXT, BLOB and ANY, and it also keeps NULLs out of its primary key, which ends the quirk of the first lesson of this module. PostgreSQL is strict about types too, with one difference worth knowing: it refuses 'abc' for an integer column, but when it stores 91.5 in an integer column it converts the number to 92 instead of refusing it.

Changing the constraints of a table that holds data

A constraint added to a full table must check the rows already there. In PostgreSQL, ALTER TABLE … ADD CONSTRAINT scans the table while it blocks other writes. With NOT VALID, the scan is skipped and new rows are checked at once; VALIDATE CONSTRAINT checks the old rows later, while other transactions keep writing. This works for foreign key, CHECK and not-null constraints, and it is the usual way to add a rule to a large, busy table.

SQLite’s ALTER TABLE could not add or remove the NOT NULL and CHECK constraints of existing columns until version 3.53.0, whose release notes list “adding and removing NOT NULL and CHECK constraints”, for example with ALTER TABLE marks ALTER COLUMN score SET NOT NULL. The SQLite inside the browser runner here is version 3.49.1, so the example shows the error you get there, and the rebuild that works in every version:

CREATE TABLE marks (roll_no INTEGER NOT NULL PRIMARY KEY, score INTEGER);
INSERT INTO marks VALUES (101, 72), (102, 64);

-- SQLite 3.53.0 added this form; the SQLite 3.49.1 inside sql.js does not know it.
ALTER TABLE marks ALTER COLUMN score SET NOT NULL;

-- The way that works everywhere: build the new shape, copy the rows, swap the names.
CREATE TABLE marks_new (roll_no INTEGER NOT NULL PRIMARY KEY, score INTEGER NOT NULL);
INSERT INTO marks_new SELECT roll_no, score FROM marks;
DROP TABLE marks;
ALTER TABLE marks_new RENAME TO marks;
INSERT INTO marks VALUES (103, NULL);
SELECT name, "notnull" FROM pragma_table_info('marks');

┌─────────┬─────────┐
│  name   │ notnull │
├─────────┼─────────┤
│ roll_no │ 1       │
│ score   │ 1       │
└─────────┴─────────┘

Parse error near line 5: near "ALTER": syntax error
Runtime error near line 12: NOT NULL constraint failed: marks.score

The rebuild creates a table of the new shape, copies the rows, drops the old table and renames the new one; the catalog then shows score as NOT NULL and the next NULL is refused. On a real database, run those steps inside one transaction, and recreate any indexes, triggers and views that belonged to the old table.

Rules across rows: no overlapping bookings

“A room cannot be booked twice for the same time” is a rule about pairs of rows, and none of the constraints so far can say it: UNIQUE (room, start_time) still lets 10:00 to 12:00 and 11:00 to 13:00 both in. PostgreSQL 18 adds temporal constraints for this. A primary key or unique constraint whose last column is a range can end with WITHOUT OVERLAPS:

• PRIMARY KEY (room, during WITHOUT OVERLAPS), where during is a tstzrange, allows many bookings per room as long as their ranges do not overlap. PostgreSQL enforces it like the exclusion constraint EXCLUDE USING gist (room WITH =, during WITH &&).
• The other columns must be comparable for equality in a GiST index, so a text column such as room needs the btree_gist extension; without it, PostgreSQL 18 refuses to create the table because text has no default GiST operator class.
• An overlapping insert is refused with “conflicting key value violates exclusion constraint”, and the message shows both bookings. Ranges such as [10:00, 12:00) include their start but not their end, so a booking from 12:00 is accepted next to one that ends at 12:00.
• A foreign key can refer to such a key with PERIOD: FOREIGN KEY (room, PERIOD during) REFERENCES room_rate (room, PERIOD valid) requires the referenced rows’ periods, taken together, to cover the whole period of the referring row. Temporal foreign keys support only NO ACTION; PostgreSQL refuses CASCADE, SET NULL, SET DEFAULT and RESTRICT for them.

SQLite has nothing similar, so the rule needs a trigger that looks for an overlapping row before each insert:

-- Times are minutes after midnight; a booking runs from its start up to, not including, its end.
CREATE TABLE booking (
  booking_id INTEGER PRIMARY KEY,
  room       TEXT    NOT NULL,
  start_min  INTEGER NOT NULL,
  end_min    INTEGER NOT NULL,
  CHECK (start_min < end_min)
);

-- SQLite cannot declare "no two bookings of a room overlap", so a trigger checks every insert.
CREATE TRIGGER booking_no_overlap
BEFORE INSERT ON booking
WHEN EXISTS (
  SELECT 1 FROM booking AS b
  WHERE b.room = NEW.room AND b.start_min < NEW.end_min AND NEW.start_min < b.end_min
)
BEGIN
  SELECT RAISE(ABORT, 'room already booked for part of that time');
END;

INSERT INTO booking (room, start_min, end_min) VALUES ('R101', 600, 720);  -- 10:00 to 12:00
INSERT INTO booking (room, start_min, end_min) VALUES ('R101', 720, 780);  -- 12:00 to 13:00: touches only
INSERT INTO booking (room, start_min, end_min) VALUES ('R101', 690, 750);  -- 11:30 to 12:30: overlaps
INSERT INTO booking (room, start_min, end_min) VALUES ('R102', 690, 750);  -- the same time, another room

SELECT room,
       printf('%02d:%02d', start_min / 60, start_min % 60) AS starts,
       printf('%02d:%02d', end_min / 60, end_min % 60)     AS ends
FROM booking ORDER BY room, start_min;

┌──────┬────────┬───────┐
│ room │ starts │ ends  │
├──────┼────────┼───────┤
│ R101 │ 10:00  │ 12:00 │
│ R101 │ 12:00  │ 13:00 │
│ R102 │ 11:30  │ 12:30 │
└──────┴────────┴───────┘

Runtime error near line 23: room already booked for part of that time

The trigger let in the booking that only touches the first one at 12:00 and the same time in another room, and refused the booking that overlaps. Two bookings overlap when each starts before the other ends; that one condition covers every way two ranges can overlap. A complete solution also needs a BEFORE UPDATE trigger, or a booking could be moved onto another.

A trigger or an application check is weaker than a declared constraint when several transactions write at once. PostgreSQL’s documentation calls it “very difficult to enforce business rules regarding data integrity” in Read Committed transactions, the default, because two concurrent transactions do not see each other’s uncommitted rows: both checks can find no overlap, and both bookings get in. An exclusion or temporal constraint is checked through its index, which does see concurrent insertions. SQLite avoids this race only because it allows “one writer at any instant in time”. The SQL standard also defines CREATE ASSERTION for rules that span tables, but PostgreSQL lists assertions among the features it does not support, and it answers the statement with “CREATE ASSERTION is not yet implemented”.

Constraints the database does not enforce

Sometimes a rule is declared but not enforced. PostgreSQL 18 lets CHECK and foreign key constraints be declared NOT ENFORCED; its documentation says the database then “will not check the constraint”, leaving it to the application, which makes such a constraint documentation that tools can read rather than a guarantee. SQLite has per-connection switches that go further: foreign keys are off unless enabled, PRAGMA ignore_check_constraints = ON skips every CHECK, and setting defer_foreign_keys back to 0 in the middle of a transaction can let it commit with violations, as its documentation cautions. These are the gaps that Codd’s rules 10 and 12 were written to close. Treat any of them as a temporary measure, for a bulk load whose data you check afterwards, never as a normal setting.

Interview questions

What are entity integrity and referential integrity?

They are the two integrity rules that Codd stated for every relational database. Entity integrity says that no part of a primary key may be NULL, because the key is what tells rows apart and an unknown value cannot do that; SQL enforces it with PRIMARY KEY, which implies NOT NULL (in SQLite, add NOT NULL or use a STRICT table). Referential integrity says that every non-NULL foreign key value must match the key of an existing row in the table it refers to. SQL enforces it with FOREIGN KEY constraints, which check inserts and updates of the child and, through their ON DELETE and ON UPDATE actions, deletes and key changes of the parent.

Key takeaways

• Declare rules as constraints so that every program obeys them: Codd’s rule 10, integrity independence.
• UNIQUE allows any number of NULLs by default, and CHECK passes when its condition is unknown; add NOT NULL when a value is required.
• A CHECK sees one row; rules across rows need exclusion or temporal constraints, triggers or application code.
• Pick the referential action from the meaning of the data: CASCADE for parts, SET NULL for optional links, RESTRICT or NO ACTION to refuse. Only NO ACTION can wait for a deferred check.
• In SQLite, run PRAGMA foreign_keys = ON on every connection and PRAGMA foreign_key_check after migrations.
• Deferred constraints let circular references be inserted in one transaction; a failed COMMIT leaves an SQLite transaction open but rolls a PostgreSQL one back.
• STRICT tables make SQLite refuse values of the wrong type; PostgreSQL refuses wrong text but rounds 91.5 to 92 in an integer column.
• PostgreSQL 18’s WITHOUT OVERLAPS and PERIOD express “no overlapping bookings” and “covered for the whole period” as declared constraints.