FK constraint trigger design

FK constraints basically have the following form:

The syntax is more elaborate than indicated above, but this is sufficient for this discussion. Note: although SQLite parses the ON INSERT clause, it is not entirely clear what DRH means by that clause, as it does not exist on other popular databases.

A FK enforces a parent-child relationship between tables. If a field in a child table has a foreign key to field in a master table, it is enforced that the master table has a matching entry. An example would be two tables, order and order_line. The order_line table has a field order_id which references the field id in the order table. Each order line must be part of an order, so each row in the order_line table must refer to a corresponding entry in the order table. If a change is made to the order_line table, the FK constraint checks that the value for order_id in the new or updated row actually exists. If not, an error is raised. Typically, the master column(s) form a unique (or primary) key on the master table.

The ref-args qualifiers determine what should happen when updates or deletes occur on the master ('order') table. The default action is RESTRICT, which means, if there are dependend rows in the child ('order_line') table, that the statement is rejected. When it is SET NULL, the relevant field in the child table is updated to NULL. When it is SET DEFAULT, the relevant field is set to the default. In both cases, the new value must exist in the master table otherwise an exception is still thrown. The last option, CASCADE, cascades the new value to all depedent rows in the child table.

The above behaviors are satisfied by generating four triggers for each FK constraint:

• one to check inserts on the child table,
• one to check updates on the child table,
• one to check updates on the master table, and
• one to check deletes on the master table

sqlite> create table master(id integer primary key);
sqlite> create table child(fld integer,
          master_id integer references master(id) on delete cascade on update set null);


CREATE TRIGGER sqlite_0_4_0_i
  BEFORE INSERT ON child
  FOR EACH ROW
BEGIN
  SELECT
    CASE WHEN ((SELECT ROWID FROM master WHERE id=NEW.master_id) IS NULL)
         THEN RAISE(ABORT, 'INSERT on table "child" violates foreign key constraint "child_0"')
    END;
END;

CREATE TRIGGER sqlite_0_4_0_u
  BEFORE UPDATE ON child
  FOR EACH ROW
BEGIN
  SELECT
    CASE WHEN ((SELECT ROWID FROM master WHERE id=NEW.master_id) IS NULL)
         THEN RAISE(ABORT, 'INSERT on table "child" violates foreign key constraint "child_0"')
    END;
END;

CREATE TRIGGER sqlite_0_4_0_d
  BEFORE DELETE ON master
  FOR EACH ROW
BEGIN
  DELETE FROM child WHERE OLD.id=master_id;
END;

CREATE TRIGGER sqlite_0_4_0_v
  BEFORE UPDATE ON master
  FOR EACH ROW
BEGIN
  UPDATE child SET master_id=NULL WHERE OLD.id=master_id;
END;

Note that the above triggers are generated, but not visible in the sqlite_master table. In that table the triggers are defined in the table definitions (the FK constraint clauses) and hence do not need their own entries. More information on which triggers are generated for which FK constraints can be found in the key source file, refinteg.c