這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)怎么在PostgreSQL中查看表的主外鍵，文章內(nèi)容豐富且以專(zhuān)業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

成都創(chuàng)新互聯(lián)憑借專(zhuān)業(yè)的設(shè)計(jì)團(tuán)隊(duì)扎實(shí)的技術(shù)支持、優(yōu)質(zhì)高效的服務(wù)意識(shí)和豐厚的資源優(yōu)勢(shì)，提供專(zhuān)業(yè)的網(wǎng)站策劃、成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站、網(wǎng)站優(yōu)化、軟件開(kāi)發(fā)、網(wǎng)站改版等服務(wù)，在成都十多年的網(wǎng)站建設(shè)設(shè)計(jì)經(jīng)驗(yàn)，為成都上千多家中小型企業(yè)策劃設(shè)計(jì)了網(wǎng)站。

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

constraint_type有四種：

UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY

通過(guò)修改上邊sql語(yǔ)句的table_name和constraint_type來(lái)進(jìn)行相應(yīng)的查詢(xún)

補(bǔ)充：PostgreSQL查詢(xún)約束和創(chuàng)建刪除約束

查詢(xún)約束constraint

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

constraint_type有四種：

UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY, 通過(guò)修改上邊sql語(yǔ)句的table_name和constraint_type來(lái)進(jìn)行相應(yīng)的查詢(xún)。

添加約束

ALTER TABLE table_name ADD CONSTRAINT uk_users_name1 UNIQUE (NAME);

刪除約束

alter table table_name drop constraint if EXISTS uk_users_name1;

補(bǔ)充：PostgreSQL的依賴(lài)約束（系統(tǒng)表pg_depend和pg_constraint）詳解

pg_depend是postgres的一張系統(tǒng)表，用來(lái)記錄數(shù)據(jù)庫(kù)對(duì)象之間的依賴(lài)關(guān)系，除了常見(jiàn)的主外鍵，還有其他一些內(nèi)部依賴(lài)關(guān)系，可以通過(guò)這個(gè)系統(tǒng)表呈現(xiàn)出來(lái)。

一、表結(jié)構(gòu)：

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  |       | 對(duì)象OID
 objsubid  | integer | not null | plain  |       |
 refclassid | oid   | not null | plain  |       | 引用系統(tǒng)OID
 refobjid  | oid   | not null | plain  |       | 引用對(duì)象ID
 refobjsubid | integer | not null | plain  |       |
 deptype   | "char" | not null | plain  |       | pg_depend類(lèi)型
Indexes:
  "pg_depend_depender_index" btree (classid, objid, objsubid)
  "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
Has OIDs: no

--BTW:OID是Object Identifier的縮寫(xiě)，是對(duì)象ID的意思，因?yàn)槭菬o(wú)符號(hào)的4字節(jié)類(lèi)型，不夠足夠大，所以一般不用來(lái)做主鍵使用，僅系統(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    -- 約束類(lèi)型
 condeferrable | boolean   | not null    -- 約束是否可以推遲
 condeferred  | boolean   | not null    -- 缺省情況下，約束是否可以推遲
 convalidated | boolean   | not null    -- 約束是否經(jīng)過(guò)驗(yàn)證
 conrelid   | oid     | not null    -- 約束所在的表的OID
 contypid   | oid     | not null    -- 約束所在的域的OID
 conindid   | oid     | not null    -- 如果是、主鍵、外鍵或排除約束，則為支持這個(gè)約束的索引；否則為0
 confrelid   | oid     | not null    -- 如果是外鍵，則為參考的表；否則為 0
 confupdtype  | "char"    | not null    -- 外鍵更新操作代碼
 confdeltype  | "char"    | not null    -- 外鍵刪除操作代碼
 confmatchtype | "char"    | not null    -- 外鍵匹配類(lèi)型
 conislocal  | boolean   | not null    
 coninhcount  | integer   | not null    -- 約束直接繼承祖先的數(shù)量
 connoinherit | boolean   | not null    
 conkey    | smallint[]  |     -- 如果是表約束（包含外鍵，但是不包含約束觸發(fā)器），則是約束字段的列表
 confkey    | smallint[]  |     -- 如果是一個(gè)外鍵，是參考的字段的列表
 conpfeqop   | oid[]    |     -- 如果是一個(gè)外鍵，是PK = FK比較的相等操作符的列表
 conppeqop   | oid[]    |    -- 如果是一個(gè)外鍵，是PK = PK比較的相等操作符的列表
 conffeqop   | oid[]    |     -- 如果是一個(gè)外鍵，是FK = FK比較的相等操作符的列表
 conexclop   | oid[]    |     -- 如果是一個(gè)排除約束，是每個(gè)字段排除操作符的列表
 conbin    | pg_node_tree |     -- 如果是一個(gè)檢查約束，那就是其表達(dá)式的內(nèi)部形式
 consrc    | text     |     -- 如果是檢查約束，則是表達(dá)式的人類(lèi)可讀形式
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字段類(lèi)型9.1之后多了一個(gè)extension的類(lèi)型，目前類(lèi)型有

DEPENDENCY_NORMAL (n)   ：普通的依賴(lài)對(duì)象，如表與schema的關(guān)系
DEPENDENCY_AUTO (a)    ：自動(dòng)的依賴(lài)對(duì)象，如主鍵約束
DEPENDENCY_INTERNAL (i)  ：內(nèi)部的依賴(lài)對(duì)象，通常是對(duì)象本身
DEPENDENCY_EXTENSION (e) ：9.1新增的的擴(kuò)展依賴(lài)
DEPENDENCY_PIN (p)    ：系統(tǒng)內(nèi)置的依賴(lài)

二、例子

wiki上有一個(gè)SQL可以列出系統(tǒng)和用戶(hù)對(duì)象的各種依賴(lài)關(guān)系,低版本的可以看wiki上的另一個(gè)寫(xiě)法

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后面加個(gè)條件 and deptype <>'i' 排除internal依賴(lài)

建一張普通的表，執(zhí)行上面的SQL

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)

--普通用戶(hù)來(lái)看只是建了個(gè)表，但是沒(méi)有約束，其實(shí)因?yàn)檫@個(gè)表是建立在schema下面，表是依賴(lài)于schema上面的

加一個(gè)主鍵約束

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)

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

三、非正常刪除

正常情況下用戶(hù)刪除有依賴(lài)關(guān)系的對(duì)象時(shí)會(huì)提示需要先刪除最里層沒(méi)依賴(lài)的對(duì)象，但是如果通過(guò)刪除系統(tǒng)表，但又刪得不對(duì)，就會(huì)導(dǎo)致異常，比如上面這個(gè)例子會(huì)出現(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)這個(gè)問(wèn)題，是因?yàn)槭止ぐ鸭s束對(duì)象刪除了，但是在pg_depend依賴(lài)關(guān)系里面卻仍然存在關(guān)系，所以刪除該表時(shí)發(fā)現(xiàn)最里層的依賴(lài)對(duì)象找不到了就報(bào)錯(cuò)了，

解決：

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

2.刪除該表所有的系統(tǒng)依賴(lài)信息 上面的問(wèn)題需要?jiǎng)h除

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

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

上述就是小編為大家分享的怎么在PostgreSQL中查看表的主外鍵了，如果剛好有類(lèi)似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

本文標(biāo)題：怎么在PostgreSQL中查看表的主外鍵-創(chuàng)新互聯(lián)
文章路徑：http://sd-ha.com/article38/dpcipp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、Google、軟件開(kāi)發(fā)、做網(wǎng)站、網(wǎng)站營(yíng)銷(xiāo)、靜態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)