是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
另一种方式:
WITH foreign_keys AS (
SELECT
conname,
conrelid,
confrelid,
unnest(conkey) AS conkey,
unnest(confkey) AS confkey
FROM pg_constraint
WHERE contype = 'f' -- AND confrelid::regclass = 'your_table'::regclass
)
-- if confrelid, conname pair shows up more than once then it is multicolumn foreign key
SELECT fk.conname as constraint_name,
fk.confrelid::regclass as referenced_table, af.attname as pkcol,
fk.conrelid::regclass as referencing_table, a.attname as fkcol
FROM foreign_keys fk
JOIN pg_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid
JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid
ORDER BY fk.confrelid, fk.conname
;
其他回答
我自己的贡献。目标是备份所有外键的定义:
SELECT
'ALTER TABLE ' || tc.table_schema || '.' || tc.table_name || E'\n
ADD FOREIGN KEY (' || kcu.column_name || ')' || E'\n
REFERENCES ' || ccu.table_schema || '.' || ccu.table_name ||
' (' || ccu.column_name || ') ' || E'\n ' ||
CASE WHEN rc.match_option <> 'NONE' THEN E'\n
MATCH ' || rc.match_option ELSE '' END ||
CASE WHEN rc.update_rule <> 'NO ACTION' THEN E'\n
ON UPDATE ' || rc.update_rule || ' ' ELSE '' END ||
CASE WHEN rc.delete_rule <> 'NO ACTION'
THEN 'ON DELETE ' || rc.delete_rule ELSE '' END || ';'
AS add_constraint
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
JOIN information_schema.referential_constraints AS rc
ON tc.constraint_name=rc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
\t\a\g\a\ta
注意:在读取约束列时,不要忘记列的顺序!
SELECT conname, attname
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE attrelid = 'schema.table_name'::regclass
ORDER BY conname, array_position(c.conkey, a.attnum)
PSQL就是这样做的,如果你用:
psql -E
它将准确地显示执行了哪些查询。在查找外键的情况下,它是:
SELECT conname,
pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16485' AND r.contype = 'f' ORDER BY 1
在这种情况下,16485是我正在寻找的表的oid -你可以通过将你的表名转换为regclass来获得它:
WHERE r.conrelid = 'mytable'::regclass
如果表名不是唯一的(或者是搜索路径中的第一个),则对表名进行模式限定:
WHERE r.conrelid = 'myschema.mytable'::regclass
另一种方式:
WITH foreign_keys AS (
SELECT
conname,
conrelid,
confrelid,
unnest(conkey) AS conkey,
unnest(confkey) AS confkey
FROM pg_constraint
WHERE contype = 'f' -- AND confrelid::regclass = 'your_table'::regclass
)
-- if confrelid, conname pair shows up more than once then it is multicolumn foreign key
SELECT fk.conname as constraint_name,
fk.confrelid::regclass as referenced_table, af.attname as pkcol,
fk.conrelid::regclass as referencing_table, a.attname as fkcol
FROM foreign_keys fk
JOIN pg_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid
JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid
ORDER BY fk.confrelid, fk.conname
;
SELECT
conrelid::regclass AS table_from,
conname,
pg_get_constraintdef(oid) as condef
FROM pg_catalog.pg_constraint r
也适用于所有约束条件。例如,使用pysql: