MySQL
information_schema.KEY_COLUMN_USAGE 存储了整个数据库实例的外键信息。
按数据库查询表关联关系,建议把下面的换内连接查询,提高查询速度。
--CONSTRAINT_NAME 约束名称, TABLE_NAME 表名(外键表), COLUMN_NAME 列名(外键列),
--REFERENCED_TABLE_NAME 引用表(主键表), REFERENCED_COLUMN_NAME 引用列(主键列)
select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
from information_schema.KEY_COLUMN_USAGE t
where t.CONSTRAINT_NAME in
(SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS a where a.CONSTRAINT_SCHEMA=?)
Oracle
通过约束名称关联到user_constraints、user_cons_columns 两表查找。
--constraint_name 约束名称, r_table_name 表名(外键表), r_column_name 列名(外键列),
--p_table_name 引用表(主键表), p_column_name引用列(主键列)
select a.constraint_name,
a.table_name p_table_name,
b.column_name p_column_name,
c.table_name r_table_name,
d.column_name r_column_name
from user_constraints a,
user_cons_columns b,
user_constraints c,
user_cons_columns d
where a.constraint_name = b.constraint_name
and c.R_CONSTRAINT_NAME = a.constraint_name
and c.constraint_name = d.constraint_name
and a.constraint_type = 'P'
and c.constraint_type = 'R'
user_constraints .constraint_type对应的含义
TypeCode Type | Description | Acts On Level |
---|---|---|
C | Check on a table | Column |
O | Read Only on a view | Object |
P | Primary Key | Object |
R | Referential AKA Foreign Key | Column |
U | Unique Key | Column |
V | Check Option on a view | Object |
SQL Server
sysforeignkeys 存储了外键信息,sysobjects 对象信息。
可以直接改成内连接查询。
--name约束名称, r_table_name 表名(外键表), r_column_name 列名(外键列),
--p_table_name 引用表(主键表), p_column_name引用列(主键列)
SELECT
a.name,
object_name(b.rkeyid) p_table_name,
(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid) p_column_name,
object_name(b.fkeyid) r_table_name,
(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) r_column_name
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
sysobjects.xtype含义
xtype值 | 含义 |
---|---|
C | CHECK 约束 |
D | 默认值或 DEFAULT 约束 |
F | FOREIGN KEY 约束 |
L | 日志 |
FN | 标量函数 |
IF | 内嵌表函数 |
P | 存储过程 |
PK | PRIMARY KEY 约束(类型是 K) |
RF | 复制筛选存储过程 |
S | 系统表 |
TF | 表函数 |
TR | 触发器 |
U | 用户表 |
UQ | UNIQUE 约束(类型是 K) |
V | 视图 |
X | 扩展存储过程 |
PostgreSQL
--constraint_name约束名称, table_name表名(外键表), column_name列名(外键列),
--foreign_table_name 引用表(主键表), foreign_column_name引用列(主键列)
select
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name,
tc.is_deferrable,
tc.initially_deferred
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' and tc.constraint_catalog=? and tc.table_schema=?