域名預(yù)訂/競價，好“米”不錯過

這篇文章主要介紹了PostgreSQL 查看表的主外鍵等約束關(guān)系詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧。

我就廢話不多說了，大家還是直接看代碼吧~

postgres=# \d+ pg_depend
            Table "pg_catalog.pg_depend"
  Column  | Type  | Modifiers | Storage | Stats target | Description
-------------+---------+-----------+---------+--------------+-------------
 classid   | oid   | not null | plain  |       | 系統(tǒng)OID
 objid    | oid   | not null | plain  |       | 對象OID
 objsubid  | integer | not null | plain  |       |
 refclassid | oid   | not null | plain  |       | 引用系統(tǒng)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字節(jié)類型，不夠足夠大，所以一般不用來做主鍵使用，僅系統(tǒng)內(nèi)部，比如系統(tǒng)表等應(yīng)用，可以與一些整型數(shù)字進(jìn)行轉(zhuǎn)換。與之相關(guān)的系統(tǒng)參數(shù)是default_with_oids，默認(rèn)是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    -- 約束是否經(jīng)過驗證
 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    -- 約束直接繼承祖先的數(shù)量
 connoinherit | boolean   | not null   
 conkey    | smallint[]  |     -- 如果是表約束（包含外鍵，但是不包含約束觸發(fā)器），則是約束字段的列表
 confkey    | smallint[]  |     -- 如果是一個外鍵，是參考的字段的列表
 conpfeqop   | oid[]    |     -- 如果是一個外鍵，是PK = FK比較的相等操作符的列表
 conppeqop   | oid[]    |    -- 如果是一個外鍵，是PK = PK比較的相等操作符的列表
 conffeqop   | oid[]    |     -- 如果是一個外鍵，是FK = FK比較的相等操作符的列表
 conexclop   | oid[]    |     -- 如果是一個排除約束，是每個字段排除操作符的列表
 conbin    | pg_node_tree |     -- 如果是一個檢查約束，那就是其表達(dá)式的內(nèi)部形式
 consrc    | text     |     -- 如果是檢查約束，則是表達(dá)式的人類可讀形式
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 -- 約束直接繼承祖先的數(shù)量 connoinherit | boolean | not null

conkey | smallint[] | -- 如果是表約束（包含外鍵，但是不包含約束觸發(fā)器），則是約束字段的列表 confkey | smallint[] | -- 如果是一個外鍵，是參考的字段的列表 conpfeqop | oid[] | -- 如果是一個外鍵，是PK = FK比較的相等操作符的列表 conppeqop | oid[] | -- 如果是一個外鍵，是PK = PK比較的相等操作符的列表 conffeqop | oid[] | -- 如果是一個外鍵，是FK = FK比較的相等操作符的列表 conexclop | oid[] | -- 如果是一個排除約束，是每個字段排除操作符的列表 conbin | pg_node_tree | -- 如果是一個檢查約束，那就是其表達(dá)式的內(nèi)部形式 consrc | text | -- 如果是檢查約束，則是表達(dá)式的人類可讀形式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的關(guān)系
DEPENDENCY_AUTO (a)    ：自動的依賴對象，如主鍵約束
DEPENDENCY_INTERNAL (i)  ：內(nèi)部的依賴對象，通常是對象本身
DEPENDENCY_EXTENSION (e) ：9.1新增的的擴(kuò)展依賴
DEPENDENCY_PIN (p)    ：系統(tǒng)內(nèi)置的依賴

(p) ：系統(tǒng)內(nèi)置的依賴

二、例子

wiki上有一個SQL可以列出系統(tǒng)和用戶對象的各種依賴關(guān)系,低版本的可以看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 TABLE
postgres=# 執(zhí)行上面的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)

--多了一個約束的信息，下面的這條信息表明這個主鍵約束是依賴于表上的，并且是自動模式，詳細(xì)信息可以在系統(tǒng)表pg_constrant里面查詢

三、非正常刪除

正常情況下用戶刪除有依賴關(guān)系的對象時會提示需要先刪除最里層沒依賴的對象，但是如果通過刪除系統(tǒng)表，但又刪得不對，就會導(dǎo)致異常，比如上面這個例子會出現(xiàn) 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=#

--出現(xiàn)這個問題，是因為手工把約束對象刪除了，但是在pg_depend依賴關(guān)系里面卻仍然存在關(guān)系，所以刪除該表時發(fā)現(xiàn)最里層的依賴對象找不到了就報錯了，

解決：

1.手工恢復(fù)該表的約束對象，比較難也比較煩

2.刪除該表所有的系統(tǒng)依賴信息 上面的問題需要刪除

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

3.要說一點的是不要去手工刪除一些系統(tǒng)表信息來達(dá)到刪除約束的目的，容易因刪不干凈而造成各種異常

文章來源：腳本之家

來源地址：https://www.jb51.net/article/204809.htm

申請創(chuàng)業(yè)報道，分享創(chuàng)業(yè)好點子。點擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！