是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
SELECT
conrelid::regclass AS table_from,
conname,
pg_get_constraintdef(oid) as condef
FROM pg_catalog.pg_constraint r
也适用于所有约束条件。例如,使用pysql:
其他回答
以下是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;
您可以通过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';
在PostgreSQL提示符上发出\d+ tablename,除了显示表列的数据类型外,它还会显示索引和外键。
正确的解决方案,使用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
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的