PostgreSQL 数据库基础 如何查看表的主外键等约束关系详解

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’;

UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY

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’;

ALTER TABLE table_name ADD CONSTRAINT uk_users_name1 UNIQUE (NAME);

alter table table_name drop constraint if EXISTS uk_users_name1;

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

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)

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

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);

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)

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)

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=#

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