• 注册
    • 查看作者
    • Postgresql 查询表引用或被引用的外键操作

        

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

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

        废话不多说,直接上实验!

        CentOS 7 + PG 10

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

        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

        插入数据

        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.

        查询主表被哪个子表引用。如果结果为空,说明没有任何子表引用的该表。

        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)

        查询子表引用的哪个主表。如果结果为空,说明没有任何引用主表。

        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(系统所有约束表)

        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)

        示例:

        

        文章来源:脚本之家

        来源地址:https://www.jb51.net/article/205219.htm

        

    • 0
    • 0
    • 0
    • 5
    • 请登录之后再进行评论

      登录
    • 单栏布局 侧栏位置: