Referential integrity is one of the foundational ideas in relational database design, and foreign keys are how PostgreSQL enforces it. Their job is to stop the database from storing inconsistent data by constraining how rows in one table may refer to rows in another.
The trade-off is that keys introduce ordering requirements. Without any foreign keys, rows can be inserted into any table in any sequence — PostgreSQL places no restrictions. Once a key is in place, the sequence in which data arrives starts to matter.
A minimal model and the failure it produces
Consider a small schema holding currencies, products and product descriptions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
CREATE TABLE t_currency ( id int, shortcut char (3), PRIMARY KEY (id) ); CREATE TABLE t_location ( id int, location_name text, PRIMARY KEY (id) ); CREATE TABLE t_product ( id int, name text, currency_id int REFERENCES t_currency (id), PRIMARY KEY (id) ); CREATE TABLE t_product_desc ( id int, product_id int REFERENCES t_product (id), description text, PRIMARY KEY (id) ); CREATE TABLE t_product_stock ( product_id int REFERENCES t_product (id), location_id int REFERENCES t_location (id), amount numeric CHECK (amount >= 0) ); |
Nothing unusual here. Inserting into the product table is where the problem appears:
|
1 2 3 4 5 6 |
test=# INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1); ERROR: insert or update on table 't_product' violates foreign key constraint 't_product_currency_id_fkey' DETAIL: Key (currency_id)=(1) is not present in table 't_currency'. test=# INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', NULL); INSERT 0 1 |
The first INSERT cannot succeed: currency 1 does not exist yet. The alternative is to write a NULL, meaning an unknown currency. In the default case, then, the currency table has to be populated before locations, and so on down the line. Order is not optional.
Deriving the order from the catalog
Working with a pre-existing model makes this harder to reason about, and loading an empty schema can become genuinely fiddly. Rather than trace the dependencies by hand, the insertion order can be produced by a query against the PostgreSQL system tables, which already hold everything needed to work it out:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
WITH RECURSIVE fkeys AS ( /* source and target tables for all foreign keys */ SELECT conrelid AS source, confrelid AS target FROM pg_constraint WHERE contype = 'f' ), tables AS ( ( /* all tables ... */ SELECT oid AS table_name, 1 AS level, ARRAY[oid] AS trail, FALSE AS circular FROM pg_class WHERE relkind = 'r' AND NOT relnamespace::regnamespace::text LIKE ANY (ARRAY['pg_catalog', 'information_schema', 'pg_temp_%']) EXCEPT /* ... except the ones that have a foreign key */ SELECT source, 1, ARRAY[ source ], FALSE FROM fkeys ) UNION ALL /* all tables with a foreign key pointing a table in the working set */ SELECT fkeys.source, tables.level + 1, tables.trail || fkeys.source, tables.trail @> ARRAY[fkeys.source] FROM fkeys JOIN tables ON tables.table_name = fkeys.target /* * Stop when a table appears in the trail the third time. * This way, we get the table once with 'circular = TRUE'. */ WHERE cardinality(array_positions(tables.trail, fkeys.source)) < 2 ), ordered_tables AS ( /* get the highest level per table */ SELECT DISTINCT ON (table_name) table_name, level, circular FROM tables ORDER BY table_name, level DESC ) SELECT table_name::regclass, level FROM ordered_tables WHERE NOT circular ORDER BY level, table_name; |
The query is dense, but the output is what matters:
|
1 2 3 4 5 6 7 8 |
table_name | level -----------------+------- t_currency | 1 t_location | 1 t_product | 2 t_product_desc | 3 t_product_stock | 3 (5 rows) |
The tables come back in the required sequence: everything at level one first, then the next level, and so on. Following that order keeps referential integrity intact, provided the data itself is correct.
Deferring the check to commit
Sometimes the ordering constraint is simply inconvenient. PostgreSQL offers a way out in the form of initially deferred constraints, which move the integrity check to commit time instead of enforcing it per statement:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
BEGIN; CREATE TABLE t_currency ( id int, shortcut char (3), PRIMARY KEY (id) ); CREATE TABLE t_product ( id int, name text, currency_id int REFERENCES t_currency (id) INITIALLY DEFERRED, PRIMARY KEY (id) ); INSERT INTO t_product VALUES (1, 'PostgreSQL support', 1); INSERT INTO t_currency VALUES (1, 'EUR'); COMMIT; |
With this in place, rows can be modified in any order. As long as the transaction leaves integrity intact when it ends, PostgreSQL accepts the work, postponing the check and removing some of the burden from the developer.
