是否有一种方法使用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
;
其他回答
你可以使用PostgreSQL系统目录。也许您可以查询pg_constraint来请求外键。 您还可以使用信息模式
为了扩展Martin的精彩回答,这里有一个查询,它允许您根据父表进行过滤,并显示每个父表的子表的名称,以便您可以根据父表中的外键约束查看所有依赖的表/列。
select
con.constraint_name,
att2.attname as "child_column",
cl.relname as "parent_table",
att.attname as "parent_column",
con.child_table,
con.child_schema
from
(select
unnest(con1.conkey) as "parent",
unnest(con1.confkey) as "child",
con1.conname as constraint_name,
con1.confrelid,
con1.conrelid,
cl.relname as child_table,
ns.nspname as child_schema
from
pg_class cl
join pg_namespace ns on cl.relnamespace = ns.oid
join pg_constraint con1 on con1.conrelid = cl.oid
where con1.contype = 'f'
) con
join pg_attribute att on
att.attrelid = con.confrelid and att.attnum = con.child
join pg_class cl on
cl.oid = con.confrelid
join pg_attribute att2 on
att2.attrelid = con.conrelid and att2.attnum = con.parent
where cl.relname like '%parent_table%'
这是我目前使用的,它将列出一个表,它的fkey约束[删除表子句,它将列出当前目录中的所有表]:
SELECT
current_schema() AS "schema",
current_catalog AS "database",
"pg_constraint".conrelid::regclass::text AS "primary_table_name",
"pg_constraint".confrelid::regclass::text AS "foreign_table_name",
(
string_to_array(
(
string_to_array(
pg_get_constraintdef("pg_constraint".oid),
'('
)
)[2],
')'
)
)[1] AS "foreign_column_name",
"pg_constraint".conindid::regclass::text AS "constraint_name",
TRIM((
string_to_array(
pg_get_constraintdef("pg_constraint".oid),
'('
)
)[1]) AS "constraint_type",
pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition"
FROM pg_constraint AS "pg_constraint"
JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace
WHERE
--fkey and pkey constraints
"pg_constraint".contype IN ( 'f', 'p' )
AND
"pg_namespace".nspname = current_schema()
AND
"pg_constraint".conrelid::regclass::text IN ('whatever_table_name')
正确的解决方案,使用information_schema,使用多列键,在两个表中正确地连接不同名称的列,并且与ms sqlsever兼容:
select fks.TABLE_NAME as foreign_key_table_name
, fks.CONSTRAINT_NAME as foreign_key_constraint_name
, kcu_foreign.COLUMN_NAME as foreign_key_column_name
, rc.UNIQUE_CONSTRAINT_NAME as primary_key_constraint_name
, pks.TABLE_NAME as primary_key_table_name
, kcu_primary.COLUMN_NAME as primary_key_column_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS fks -- foreign keys
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_foreign -- the columns of the above keys
on fks.TABLE_CATALOG = kcu_foreign.TABLE_CATALOG
and fks.TABLE_SCHEMA = kcu_foreign.TABLE_SCHEMA
and fks.TABLE_NAME = kcu_foreign.TABLE_NAME
and fks.CONSTRAINT_NAME = kcu_foreign.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc -- referenced constraints
on rc.CONSTRAINT_CATALOG = fks.CONSTRAINT_CATALOG
and rc.CONSTRAINT_SCHEMA = fks.CONSTRAINT_SCHEMA
and rc.CONSTRAINT_NAME = fks.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pks -- primary keys (referenced by fks)
on rc.UNIQUE_CONSTRAINT_CATALOG = pks.CONSTRAINT_CATALOG
and rc.UNIQUE_CONSTRAINT_SCHEMA = pks.CONSTRAINT_SCHEMA
and rc.UNIQUE_CONSTRAINT_NAME = pks.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_primary
on pks.TABLE_CATALOG = kcu_primary.TABLE_CATALOG
and pks.TABLE_SCHEMA = kcu_primary.TABLE_SCHEMA
and pks.TABLE_NAME = kcu_primary.TABLE_NAME
and pks.CONSTRAINT_NAME = kcu_primary.CONSTRAINT_NAME
and kcu_foreign.ORDINAL_POSITION = kcu_primary.ORDINAL_POSITION -- this joins the columns
where fks.TABLE_SCHEMA = 'dbo' -- replace with schema name
and fks.TABLE_NAME = 'your_table_name' -- replace with table name
and fks.CONSTRAINT_TYPE = 'FOREIGN KEY'
and pks.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by fks.constraint_name, kcu_foreign.ORDINAL_POSITION
注意:在potgresql和sqlserver的information_schema实现之间存在一些差异,这使得顶部答案在两个系统上给出不同的结果——一个显示外键表的列名,另一个显示主键表的列名。出于这个原因,我决定使用KEY_COLUMN_USAGE视图。
注意:在读取约束列时,不要忘记列的顺序!
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)
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 我如何循环通过一组记录在SQL Server?
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的