postgres=# CREATETABLE t_product1
id int,
name text,
currency_id intREFERENCES t_currency (shortcut),
PRIMARY KEY (id)
ERROR: there isnouniqueconstraint matching given keys for referenced table "t_currency"
可以看到创建失败，提示“there is no unique constraint matching given keys for referenced table "t_currency"”
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".
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,
1AS level,
ARRAY[oid] AS trail,
FALSEAS circular
FROM pg_class
WHERE relkind = 'r'ANDNOT relnamespace::regnamespace::textLIKE ANY
(ARRAY['pg_catalog', 'information_schema', 'pg_temp_%'])
EXCEPT
/* ... except the ones that have a foreign key */
SELECT source,
ARRAY[ source ],
FALSEFROM 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
* Stopwhen 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 */
SELECTDISTINCTON (table_name)
table_name,
level,
circular
FROM tables
ORDERBY table_name, level DESC
SELECT table_name::regclass,
level
FROM ordered_tables
WHERENOT circular
ORDERBY level, table_name;
