Postgresql 查询表引用或被引用的外键操作

一时失言乱红尘 2022-10-26 15:29 8阅读 0赞

今天更新两个SQL。是用来查询PG中，主表被子表引用的外键，或子表引用了哪个主表的主键。

废话不多说，直接上实验！

CentOS 7 + PG 10

创建两个实验表，test01为主表，test02为子表，test02引用test01中的id列。

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
test=\# create table test01(  
test(\# id int primary key,  
test(\# col1 varchar(20)  
test(\# );  
CREATE TABLE  
  
test=\# create table test02(  
test(\# id int primary key,  
test(\# test01\_id int references test01(id),  
test(\# col1 varchar(20)  
test(\# );  
CREATE TABLE  
插入数据

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
test=\# insert into test01 values (1, 'a');  
INSERT 0 1  
test=\# insert into test01 values (2, 'b');  
INSERT 0 1  
test=\# insert into test01 values (3, 'c');  
INSERT 0 1  
test=\# insert into test02 values (1, 1, 'a');  
INSERT 0 1  
test=\# insert into test02 values (2, 1, 'a');  
INSERT 0 1  
test=\# insert into test02 values (3, 1, 'a');  
INSERT 0 1  
test=\# insert into test02 values (4, 2, 'b');  
INSERT 0 1  
test=\# insert into test02 values (5, 2, 'b');  
INSERT 0 1  
test=\# insert into test02 values (6, 11, 'b');  
ERROR: insert or update on table "test02" violates foreign key constraint "test02\_test01\_id\_fkey"  
DETAIL: Key (test01\_id)=(11) is not present in table "test01".  
查询主表被哪个子表引用。如果结果为空，说明没有任何子表引用的该表。

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
test=\# 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 ccu.table\_name='test01'; \# 输入主表  
constraint\_name | table\_name | column\_name | foreign\_table\_name | foreign\_column\_name | is\_deferrable | initially\_deferred  
\-----------------------+------------+-------------+--------------------+---------------------+---------------+--------------------  
test02\_test01\_id\_fkey | test02 | test01\_id | test01 | id | NO | NO  
(1 row)  
查询子表引用的哪个主表。如果结果为空，说明没有任何引用主表。

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
test=\# 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.table\_name='test02'; \# 输入子表  
constraint\_name | table\_name | column\_name | foreign\_table\_name | foreign\_column\_name | is\_deferrable | initially\_deferred  
\-----------------------+------------+-------------+--------------------+---------------------+---------------+--------------------  
test02\_test01\_id\_fkey | test02 | test01\_id | test01 | id | NO | NO  
(1 row)  
补充：PostgreSQL 外键引用查询

根据一个表名，查询所有外键引用它的表，以及那些外键的列名

key\_column\_usage（系统列信息表），

pg\_constraint（系统所有约束表）

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
SELECT x.table\_name,  
x.column\_name  
FROM information\_schema.key\_column\_usage x  
INNER JOIN (SELECT t.relname,  
a.conname  
FROM pg\_constraint a  
INNER JOIN pg\_class ft  
ON ft.oid = a.confrelid  
INNER JOIN pg\_class t  
ON t.oid = a.conrelid  
WHERE a.contype = 'f'  
AND a.confrelid =  
(select e.oid  
from pg\_class e  
where e.relname = 'xxx\_table')  
) tp  
ON (x.table\_name = tp.relname AND  
x.constraint\_name = tp.conname)  
示例：

以上为个人经验，希望能给大家一个参考