PostgreSQL 查看表的主外键等约束关系详解

(编辑：jimmy 日期: 2024/11/25 浏览：3 次 )

我就废话不多说了，大家还是直接看代码吧~

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 = 'your table name';

constraint_type有四种：

UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY

通过修改上边sql语句的table_name和constraint_type来进行相应的查询

补充：PostgreSQL查询约束和创建删除约束

查询约束constraint

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 = 'UNIQUE' AND tc.table_name = 'table_name';

constraint_type有四种：

UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY, 通过修改上边sql语句的table_name和constraint_type来进行相应的查询。

添加约束

ALTER TABLE table_name ADD CONSTRAINT uk_users_name1 UNIQUE (NAME);

删除约束

alter table table_name drop constraint if EXISTS uk_users_name1;

补充：PostgreSQL的依赖约束（系统表pg_depend和pg_constraint）详解

pg_depend是postgres的一张系统表，用来记录数据库对象之间的依赖关系，除了常见的主外键，还有其他一些内部依赖关系，可以通过这个系统表呈现出来。

一、表结构：

postgres=# \d+ pg_depend
            Table "pg_catalog.pg_depend"
  Column  | Type  | Modifiers | Storage | Stats target | Description
-------------+---------+-----------+---------+--------------+-------------
 classid   | oid   | not null | plain  |       | 系统OID
 objid    | oid   | not null | plain  |       | 对象OID
 objsubid  | integer | not null | plain  |       |
 refclassid | oid   | not null | plain  |       | 引用系统OID
 refobjid  | oid   | not null | plain  |       | 引用对象ID
 refobjsubid | integer | not null | plain  |       |
 deptype   | "char" | not null | plain  |       | pg_depend类型
Indexes:
  "pg_depend_depender_index" btree (classid, objid, objsubid)
  "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
Has OIDs: no

--BTW:OID是Object Identifier的缩写，是对象ID的意思，因为是无符号的4字节类型，不够足够大，所以一般不用来做主键使用，仅系统内部，比如系统表等应用，可以与一些整型数字进行转换。与之相关的系统参数是default_with_oids，默认是off

postgres=# \d pg_constraint
   Table "pg_catalog.pg_constraint"
  Column   |   Type   | Modifiers 
---------------+--------------+-----------
 conname    | name     | not null    -- 约束名
 connamespace | oid     | not null    -- 约束所在命名空间的OID
 contype    | "char"    | not null    -- 约束类型
 condeferrable | boolean   | not null    -- 约束是否可以推迟
 condeferred  | boolean   | not null    -- 缺省情况下，约束是否可以推迟
 convalidated | boolean   | not null    -- 约束是否经过验证
 conrelid   | oid     | not null    -- 约束所在的表的OID
 contypid   | oid     | not null    -- 约束所在的域的OID
 conindid   | oid     | not null    -- 如果是唯一、主键、外键或排除约束，则为支持这个约束的索引；否则为0
 confrelid   | oid     | not null    -- 如果是外键，则为参考的表；否则为 0
 confupdtype  | "char"    | not null    -- 外键更新操作代码
 confdeltype  | "char"    | not null    -- 外键删除操作代码
 confmatchtype | "char"    | not null    -- 外键匹配类型
 conislocal  | boolean   | not null    
 coninhcount  | integer   | not null    -- 约束直接继承祖先的数量
 connoinherit | boolean   | not null    
 conkey    | smallint[]  |     -- 如果是表约束（包含外键，但是不包含约束触发器），则是约束字段的列表
 confkey    | smallint[]  |     -- 如果是一个外键，是参考的字段的列表
 conpfeqop   | oid[]    |     -- 如果是一个外键，是PK = FK比较的相等操作符的列表
 conppeqop   | oid[]    |    -- 如果是一个外键，是PK = PK比较的相等操作符的列表
 conffeqop   | oid[]    |     -- 如果是一个外键，是FK = FK比较的相等操作符的列表
 conexclop   | oid[]    |     -- 如果是一个排除约束，是每个字段排除操作符的列表
 conbin    | pg_node_tree |     -- 如果是一个检查约束，那就是其表达式的内部形式
 consrc    | text     |     -- 如果是检查约束，则是表达式的人类可读形式
Indexes:
  "pg_constraint_oid_index" UNIQUE, btree (oid)
  "pg_constraint_conname_nsp_index" btree (conname, connamespace)
  "pg_constraint_conrelid_index" btree (conrelid)
  "pg_constraint_contypid_index" btree (contypid)

pg_depend.deptype字段类型9.1之后多了一个extension的类型，目前类型有

DEPENDENCY_NORMAL (n)   ：普通的依赖对象，如表与schema的关系
DEPENDENCY_AUTO (a)    ：自动的依赖对象，如主键约束
DEPENDENCY_INTERNAL (i)  ：内部的依赖对象，通常是对象本身
DEPENDENCY_EXTENSION (e) ：9.1新增的的扩展依赖
DEPENDENCY_PIN (p)    ：系统内置的依赖

二、例子

wiki上有一个SQL可以列出系统和用户对象的各种依赖关系,低版本的可以看wiki上的另一个写法

SELECT classid::regclass AS "depender object class",
  CASE classid
    WHEN 'pg_class'::regclass THEN objid::regclass::text
    WHEN 'pg_type'::regclass THEN objid::regtype::text
    WHEN 'pg_proc'::regclass THEN objid::regprocedure::text
    ELSE objid::text
  END AS "depender object identity",
  objsubid,
  refclassid::regclass AS "referenced object class",
  CASE refclassid
    WHEN 'pg_class'::regclass THEN refobjid::regclass::text
    WHEN 'pg_type'::regclass THEN refobjid::regtype::text
    WHEN 'pg_proc'::regclass THEN refobjid::regprocedure::text
    ELSE refobjid::text
  END AS "referenced object identity",
  refobjsubid,
  CASE deptype
    WHEN 'p' THEN 'pinned'
    WHEN 'i' THEN 'internal'
    WHEN 'a' THEN 'automatic'
    WHEN 'n' THEN 'normal'
  END AS "dependency type"
FROM pg_catalog.pg_depend WHERE (objid >= 16384 OR refobjid >= 16384);

BTW:我通常喜欢在where后面加个条件 and deptype <>'i' 排除internal依赖

建一张普通的表，执行上面的SQL

postgres=# create table tbl_parent(id int);
CREATE TABLE
postgres=# 执行上面的SQL;
 depender object class | depender object identity | objsubid | referenced object class | referenced object identity | refobjsubid | dependency type
-----------------------+--------------------------+----------+-------------------------+------------- pg_class       | tbl_parent        |    0 | pg_namespace      | 2200            |      0 | normal
(1 row)

--普通用户来看只是建了个表，但是没有约束，其实因为这个表是建立在schema下面，表是依赖于schema上面的

加一个主键约束

postgres=# alter table tbl_parent add primary key(id);
ALTER TABLE
 depender object class | depender object identity | objsubid | referenced object class | referenced object identity | refobjsubid | dependency type
-----------------------+--------------------------+----------+-------------------------+------- pg_class       | tbl_parent        |    0 | pg_namespace      | 2200            |      0 | normal
 pg_constraint     | 16469          |    0 | pg_class        | tbl_parent         |      1 | automatic
(2 rows)

--多了一个约束的信息，下面的这条信息表明这个主键约束是依赖于表上的，并且是自动模式，详细信息可以在系统表pg_constrant里面查询

三、非正常删除

正常情况下用户删除有依赖关系的对象时会提示需要先删除最里层没依赖的对象，但是如果通过删除系统表，但又删得不对，就会导致异常，比如上面这个例子会出现 cache lookup failed for constraint

postgres=# select oid,conname,connamespace,contype from pg_constraint where conname like 'tbl_parent%';
 oid |   conname   | connamespace | contype
-------+-----------------+--------------+---------
 16469 | tbl_parent_pkey |     2200 | p
(1 row)
 
postgres=# delete from pg_constraint where conname like 'tbl_parent%';
DELETE 1
postgres=# select oid,conname,connamespace,contype from pg_constraint where conname like 'tbl_parent%';
 oid | conname | connamespace | contype
-----+---------+--------------+---------
(0 rows)
 
postgres=# drop table tbl_parent;
ERROR: cache lookup failed for constraint 16469  --16496是约束的OID
postgres=#

--出现这个问题，是因为手工把约束对象删除了，但是在pg_depend依赖关系里面却仍然存在关系，所以删除该表时发现最里层的依赖对象找不到了就报错了，

解决：

1.手工恢复该表的约束对象，比较难也比较烦

2.删除该表所有的系统依赖信息上面的问题需要删除

postgres=# delete from pg_depend where objid = 16469 or refobjid = 16469 ;
DELETE 2
postgres=# drop table tbl_parent;
DROP TABLE

3.要说一点的是不要去手工删除一些系统表信息来达到删除约束的目的，容易因删不干净而造成各种异常

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

上一篇：PostgreSQL 性能优化之服务器参数配置操作
下一篇：Postgresql的select优化操作(快了200倍)