是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
选择的答案不为我工作,所以张贴我的sql工作。
select
con.conname as constraint_name,
src_schema.nspname as source_schema,
source.relname as source_table,
source_col.attname as source_column,
trg_schema.nspname as target_schema,
target.relname as target_table,
target_col.attname as target_column
from
pg_constraint con
inner join
pg_class source on source.oid = con.conrelid
inner join
pg_attribute source_col on source_col.attrelid = con.conrelid and source_col.attnum = con.conkey[1] and source_col.attisdropped = false
inner join
pg_namespace src_schema on src_schema.oid = source.relnamespace
inner join
pg_class target on target.oid = con.confrelid
inner join
pg_attribute target_col on target_col.attrelid = con.confrelid and target_col.attnum = con.confkey[1] and source_col.attisdropped = false
inner join
pg_namespace trg_schema on trg_schema.oid = target.relnamespace
其他回答
SELECT r.conname
,ct.table_name
,pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r, information_schema.constraint_table_usage ct
WHERE r.contype = 'f'
AND r.conname = ct.constraint_name
ORDER BY 1
正确的解决方案,使用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视图。
我升级了@ollyc的答案,目前在顶部。 我同意@fionbio,因为key_column_usage和constraint_column_usage在列级上没有相关信息。
如果constraint_column_usage具有像key_column_usage一样的ordinal_position列,则可以将其与该列连接。所以我做了一个ordinal_position到constraint_column_usage如下所示。
我无法确认手动创建的ordinal_position与key_column_usage的顺序完全相同。但我检查了一下,至少在我的箱子里是完全一样的顺序。
SELECT
tc.table_schema,
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
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 (select row_number() over (partition by table_schema, table_name, constraint_name order by row_num) ordinal_position,
table_schema, table_name, column_name, constraint_name
from (select row_number() over (order by 1) row_num, table_schema, table_name, column_name, constraint_name
from information_schema.constraint_column_usage
) t
) AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
AND ccu.ordinal_position = kcu.ordinal_position
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'mytable'
以下是Andreas Joseph Krogh从PostgreSQL邮件列表提供的解决方案:http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no
SELECT source_table::regclass, source_attr.attname AS source_column,
target_table::regclass, target_attr.attname AS target_column
FROM pg_attribute target_attr, pg_attribute source_attr,
(SELECT source_table, target_table, source_constraints[i] source_constraints, target_constraints[i] AS target_constraints
FROM
(SELECT conrelid as source_table, confrelid AS target_table, conkey AS source_constraints, confkey AS target_constraints,
generate_series(1, array_upper(conkey, 1)) AS i
FROM pg_constraint
WHERE contype = 'f'
) query1
) query2
WHERE target_attr.attnum = target_constraints AND target_attr.attrelid = target_table AND
source_attr.attnum = source_constraints AND source_attr.attrelid = source_table;
这个解决方案处理引用多个列的外键,并避免重复(其他一些答案无法做到这一点)。我唯一改变的是变量名。
下面是一个示例,返回所有引用权限表的雇员列:
SELECT source_column
FROM foreign_keys
WHERE source_table = 'employee'::regclass AND target_table = 'permission'::regclass;
你可以使用PostgreSQL系统目录。也许您可以查询pg_constraint来请求外键。 您还可以使用信息模式