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

这篇文章主要介绍了PostgreSQL查看表的主外键等约束关系详解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧。

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

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

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

postgres=#\dpg_constraint
Table"pg_catalog.pg_constraint"
Column|Type|Modifiers
---------------+--------------+-----------
conname|name|notnull--约束名
connamespace|oid|notnull--约束所在命名空间的OID
contype|"char"|notnull--约束类型
condeferrable|boolean|notnull--约束是否可以推迟
condeferred|boolean|notnull--缺省情况下，约束是否可以推迟
convalidated|boolean|notnull--约束是否经过验证
conrelid|oid|notnull--约束所在的表的OID
contypid|oid|notnull--约束所在的域的OID
conindid|oid|notnull--如果是唯一、主键、外键或排除约束，则为支持这个约束的索引；否则为0
confrelid|oid|notnull--如果是外键，则为参考的表；否则为0
confupdtype|"char"|notnull--外键更新操作代码
confdeltype|"char"|notnull--外键删除操作代码
confmatchtype|"char"|notnull--外键匹配类型
conislocal|boolean|notnull
coninhcount|integer|notnull--约束直接继承祖先的数量
connoinherit|boolean|notnull
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|notnull--约束直接继承祖先的数量connoinherit|boolean|notnull

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上的另一个写法

SELECTclassid::regclassAS"dependerobjectclass",
CASEclassid
WHEN'pg_class'::regclassTHENobjid::regclass::text
WHEN'pg_type'::regclassTHENobjid::regtype::text
WHEN'pg_proc'::regclassTHENobjid::regprocedure::text
ELSEobjid::text
ENDAS"dependerobjectidentity",
objsubid,
refclassid::regclassAS"referencedobjectclass",
CASErefclassid
WHEN'pg_class'::regclassTHENrefobjid::regclass::text
WHEN'pg_type'::regclassTHENrefobjid::regtype::text
WHEN'pg_proc'::regclassTHENrefobjid::regprocedure::text
ELSErefobjid::text
ENDAS"referencedobjectidentity",
refobjsubid,
CASEdeptype
WHEN'p'THEN'pinned'
WHEN'i'THEN'internal'
WHEN'a'THEN'automatic'
WHEN'n'THEN'normal'
ENDAS"dependencytype"
FROMpg_catalog.pg_dependWHERE(objid>=16384ORrefobjid>=16384);

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

postgres=#createtabletbl_parent(idint);
CREATETABLE
postgres=#执行上面的SQL;
dependerobjectclass|dependerobjectidentity|objsubid|referencedobjectclass|referencedobjectidentity|refobjsubid|dependencytype
-----------------------+--------------------------+----------+-------------------------+-------------pg_class|tbl_parent|0|pg_namespace|2200|0|normal
(1row)

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

加一个主键约束

postgres=#altertabletbl_parentaddprimarykey(id);
ALTERTABLE
dependerobjectclass|dependerobjectidentity|objsubid|referencedobjectclass|referencedobjectidentity|refobjsubid|dependencytype
-----------------------+--------------------------+----------+-------------------------+-------pg_class|tbl_parent|0|pg_namespace|2200|0|normal
pg_constraint|16469|0|pg_class|tbl_parent|1|automatic
(2rows)

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

三、非正常删除

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

postgres=#selectoid,conname,connamespace,contypefrompg_constraintwhereconnamelike'tbl_parent%';
oid|conname|connamespace|contype
-------+-----------------+--------------+---------
16469|tbl_parent_pkey|2200|p
(1row)

postgres=#deletefrompg_constraintwhereconnamelike'tbl_parent%';
DELETE1
postgres=#selectoid,conname,connamespace,contypefrompg_constraintwhereconnamelike'tbl_parent%';
oid|conname|connamespace|contype
-----+---------+--------------+---------
(0rows)

postgres=#droptabletbl_parent;
ERROR:cachelookupfailedforconstraint16469--16496是约束的OID
postgres=#

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

解决：

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

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

postgres=#deletefrompg_dependwhereobjid=16469orrefobjid=16469;
DELETE2
postgres=#droptabletbl_parent;
DROPTABLE

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

Tags：

转载：感谢您对杨青青个人博客网站平台的认可，以及对我们原创作品以及文章的青睐，非常欢迎各位朋友分享到个人站长或者朋友圈，但转载请说明文章出处“来源杨青个人博客”。http://www.yangqq.com/jstt/css3/474.html

很赞哦！ ()