服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - Postgresql 查询表引用或被引用的外键操作

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

2021-04-13 21:06|ChuckChen| PostgreSQL

这篇文章主要介绍了Postgresql 查询表引用或被引用的外键操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧

今天更新两个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)

示例:

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

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。如有错误或未考虑完全的地方,望不吝赐教。

原文链接:https://blog.csdn.net/chuckchen1222/article/details/83312397

延伸 · 阅读

精彩推荐
  • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

    深入理解PostgreSQL的MVCC并发处理方式

    这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

    PostgreSQL教程网3622020-04-25
  • PostgreSQLPostgreSQL标准建表语句分享

    PostgreSQL标准建表语句分享

    这篇文章主要介绍了PostgreSQL标准建表语句分享,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    码上得天下7962021-02-27
  • PostgreSQLPostgresql开启远程访问的步骤全纪录

    Postgresql开启远程访问的步骤全纪录

    postgre一般默认为本地连接,不支持远程访问,所以如果要开启远程访问,需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

    我勒个去6812020-04-30
  • PostgreSQL分布式 PostgreSQL之Citus 架构

    分布式 PostgreSQL之Citus 架构

    节点 Citus 是一种 PostgreSQL 扩展,它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群,允许 PostgreSQL 保存比单...

    未知802023-05-07
  • PostgreSQLpostgresql 中的to_char()常用操作

    postgresql 中的to_char()常用操作

    这篇文章主要介绍了postgresql 中的to_char()常用操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    J符离13432021-04-12
  • PostgreSQLpostgresql 数据库中的数据转换

    postgresql 数据库中的数据转换

    postgres8.3以后,字段数据之间的默认转换取消了。如果需要进行数据变换的话,在postgresql数据库中,我们可以用"::"来进行字段数据的类型转换。...

    postgresql教程网12482021-10-08
  • PostgreSQLPostgresql查询效率计算初探

    Postgresql查询效率计算初探

    这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一定的参考学习价...

    轨迹4622020-05-03
  • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

    RDS PostgreSQL一键大版本升级技术解密

    一、PostgreSQL行业位置 (一)行业位置 在讨论PostgreSQL(下面简称为PG)在整个数据库行业的位置之前,我们先看一下阿里云数据库在全球的数据库行业里的...

    未知1192023-05-07