PostgreSQL 查看表的主外键等约束关系详解
我就废话不多说了，大家还是直接看代码吧~
SELECT tc.constraint_name,tc.table_name,kcu.column_name, ccu.table_nameASforeign_table_name, ccu.column_nameASforeign_column_name, tc.is_deferrable,tc.initially_deferred FROM information_schema.table_constraintsAStc JOINinformation_schema.key_column_usageASkcuONtc.constraint_name=kcu.constraint_name JOINinformation_schema.constraint_column_usageASccuONccu.constraint_name=tc.constraint_name WHEREconstraint_type='FOREIGNKEY'ANDtc.table_name='yourtablename';
constraint_type有四种：
UNIQUE、PRIMARYKEY、CHECK、FOREIGNKEY
通过修改上边sql语句的table_name和constraint_type来进行相应的查询
补充：PostgreSQL查询约束和创建删除约束
查询约束constraint
SELECT tc.constraint_name,tc.table_name,kcu.column_name, ccu.table_nameASforeign_table_name, ccu.column_nameASforeign_column_name, tc.is_deferrable,tc.initially_deferred FROM information_schema.table_constraintsAStc JOINinformation_schema.key_column_usageASkcuONtc.constraint_name=kcu.constraint_name JOINinformation_schema.constraint_column_usageASccuONccu.constraint_name=tc.constraint_name WHEREconstraint_type='UNIQUE'ANDtc.table_name='table_name';
constraint_type有四种：
UNIQUE、PRIMARYKEY、CHECK、FOREIGNKEY,通过修改上边sql语句的table_name和constraint_type来进行相应的查询。
添加约束
ALTERTABLEtable_nameADDCONSTRAINTuk_users_name1UNIQUE(NAME);
删除约束
altertabletable_namedropconstraintifEXISTSuk_users_name1;
补充：PostgreSQL的依赖约束（系统表pg_depend和pg_constraint）详解
pg_depend是postgres的一张系统表，用来记录数据库对象之间的依赖关系，除了常见的主外键，还有其他一些内部依赖关系，可以通过这个系统表呈现出来。
一、表结构：
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)
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上的另一个写法
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依赖
建一张普通的表，执行上面的SQL
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.要说一点的是不要去手工删除一些系统表信息来达到删除约束的目的，容易因删不干净而造成各种异常
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。