是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
您可以通过information_schema表来实现这一点。例如:
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 information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
其他回答
我写了一个解决方案,喜欢和经常使用。代码在http://code.google.com/p/pgutils/。看这些小疙瘩。foreign_keys视图。
不幸的是,输出太冗长,这里不包括。但是,你可以在数据库的公共版本上尝试一下,就像这样:
$ psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pgutils.foreign_keys;
这至少适用于8.3版本。如果需要的话,我预计会在未来几个月内对其进行更新。
莉丝
我创建了一个小工具来查询和比较数据库模式: Dump PostgreSQL数据库模式到文本
有关于FK的信息,但ollyc的回复提供了更多的细节。
正确的解决方案,使用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视图。
您可以通过information_schema表来实现这一点。例如:
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 information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
检查ff帖子的解决方案,不要忘记标记这个,当你认为这是有帮助的
http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html
SELECT
o.conname AS constraint_name,
(SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema,
m.relname AS source_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column,
(SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS target_schema,
f.relname AS target_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column
FROM
pg_constraint o LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r');