PostgreSQL查看表的主外键等约束关系详解
项目招商找A5 快速获取精准代理名单
这篇文章主要介绍了PostgreSQL 查看表的主外键等约束关系详解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧。
我就废话不多说了，大家还是直接看代码吧~
postgres=# \d+ pg_depend Table "pg_catalog.pg_depend" Column | Type | Modifiers | Storage | Stats target | Description-------------+---------+-----------+---------+--------------+-------------classid | oid | not null | plain | | 系统OIDobjid | oid | not null | plain | | 对象OIDobjsubid | integer | not null | plain | |refclassid | oid | not null | plain | | 引用系统OIDrefobjid | oid | not null | plain | | 引用对象IDrefobjsubid | 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 -- 约束所在命名空间的OIDcontype | "char" | not null -- 约束类型condeferrable | boolean | not null -- 约束是否可以推迟condeferred | boolean | not null -- 缺省情况下，约束是否可以推迟convalidated | boolean | not null -- 约束是否经过验证conrelid | oid | not null -- 约束所在的表的OIDcontypid | oid | not null -- 约束所在的域的OIDconindid | oid | not null -- 如果是唯一、主键、外键或排除约束，则为支持这个约束的索引；否则为0confrelid | oid | not null -- 如果是外键，则为参考的表；否则为 0confupdtype | "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)
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) ：系统内置的依赖
(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依赖
postgres=# create table tbl_parent(id int);CREATE TABLEpostgres=# 执行上面的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 TABLEdepender object class | depender object identity | objsubid | referenced object class | referenced object identity | refobjsubid | dependency type-----------------------+--------------------------+----------+-------------------------+------- pg_class | tbl_parent | 0 | pg_namespace | 2200 | 0 | normalpg_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 1postgres=# 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是约束的OIDpostgres=#
--出现这个问题，是因为手工把约束对象删除了，但是在pg_depend依赖关系里面却仍然存在关系，所以删除该表时发现最里层的依赖对象找不到了就报错了，
解决：
1.手工恢复该表的约束对象，比较难也比较烦
2.删除该表所有的系统依赖信息 上面的问题需要删除
postgres=# delete from pg_depend where objid = 16469 or refobjid = 16469 ;DELETE 2postgres=# drop table tbl_parent;DROP TABLE
3.要说一点的是不要去手工删除一些系统表信息来达到删除约束的目的，容易因删不干净而造成各种异常
文章来源：脚本之家
来源地址：https://www.jb51.net/article/204809.htm
尊敬的看官您对PostgreSQL查看表的主外键等约束关系详解有什么看法呢？互联网品牌制作专家愿与您共同探讨！版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请加微信号oem365 举报，一经查实，本站将立刻删除。上一篇: CentOSPostgreSQL12主从复制(主从切换)操作 返 回 下一篇:天猫618预售定金能退吗？怎么付尾款？
