How Postgres Implements Foreign Keys

Created at: 2025-01-10

In order to implement foreign keys, Postgres creates 2 triggers in the referring table and 2 triggers in the referenced table

Referring Table Triggers

• AFTER INSERT and AFTER UPDATE row-level triggers. Such triggers verify the new row containing non-null foreign key field in the referring table points to an existing row in the referenced table.

Referred Table Triggers

• AFTER UPDATE and AFTER DELETE row-level triggers. Such triggers verify that UPDATEs and DELETEs don't culminate in invalid links in the referring table. If "CASCADING" it will also delete the orphaned row in the referring table.

Seeing The Code For The Triggers

Psql doesn't show them. But we can find them through the pg_trigger table.

SELECT tgname AS trigger_name,
       tgisinternal AS internal,
       CASE WHEN tgtype & 2 = 2 THEN 'BEFORE ' ELSE 'AFTER ' END ||
       CASE tgtype & 60
          WHEN 4  THEN 'INSERT'
          WHEN 8  THEN 'DELETE'
          WHEN 16 THEN 'UPDATE'
          ELSE '?'
       END ||
       ' FOR EACH ' ||
       CASE WHEN tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
          AS firing_conditions
FROM pg_trigger
WHERE tgrelid = '<table_name>'::regclass;

The result should show the trigger names and their firing conditions:

          trigger_name          | internal |     firing_conditions
--------------------------------+----------+---------------------------
 RI_ConstraintTrigger_a_2044997 | t        | AFTER DELETE FOR EACH ROW
 RI_ConstraintTrigger_a_2044998 | t        | AFTER UPDATE FOR EACH ROW
 RI_ConstraintTrigger_c_2044999 | t        | AFTER INSERT FOR EACH ROW
 RI_ConstraintTrigger_c_2045000 | t        | AFTER UPDATE FOR EACH ROW

The _a_ stands for action constraints, which are created in the referred table. source code

The _c_ stands for the "check" constraints, which are created in the referring table. source code

Side note about using cascade to drop the column

If you try to drop a column that is used as foreign key by another table, you'll see an error when not using CASCADE (default is RESTRICT):

BEGIN;

CREATE TABLE refered (id serial, int_field int unique);
CREATE TABLE referencing (id serial);

ALTER TABLE "referencing" ADD COLUMN "refered_int_field" int NULL
CONSTRAINT "delete_this_soon"
REFERENCES "refered"("int_field")
DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE refered DROP COLUMN int_field RESTRICT;

ROLLBACK;

ERROR:  cannot drop column int_field of table refered because other objects
depend on it.

DETAIL:  constraint delete_this_soon on table referencing depends on column
int_field of table refered

HINT:  Use DROP ... CASCADE to drop the dependent objects too.