国产伊人精品在线,国产不卡一区二区三区在线观看,91麻豆精品一二三区在线

主頁 > 知識庫 > PostgreSQL中enable、disable和validate外鍵約束的實例

PostgreSQL中enable、disable和validate外鍵約束的實例

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

postgres=# create table t1(a int primary key,b text,c date);
CREATE TABLE
postgres=# create table t2(a int primary key,b int references t1(a),c text);
CREATE TABLE
postgres=# insert into t1 (a,b,c) values(1,'aa',now());
INSERT 0 1
postgres=# insert into t1 (a,b,c) values(2,'bb',now());
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (1,1,'aa');
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (2,2,'aa');
INSERT 0 1
postgres=# \d t1
         Table "public.t1"
 Column | Type  | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a   | integer |      | not null |
 b   | text  |      |     |
 c   | date  |      |     |
Indexes:
  "t1_pkey" PRIMARY KEY, btree (a)
Referenced by:
  TABLE "t2" CONSTRAINT "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
 
postgres=# \d t2
         Table "public.t2"
 Column | Type  | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a   | integer |      | not null |
 b   | integer |      |     |
 c   | text  |      |     |
Indexes:
  "t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
  "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
 
postgres=#

假設我們想通過腳本向表中加載一些數(shù)據(jù)。因為我們不知道腳本中加載的順序，我們決定將表t2上的外鍵約束禁用掉，在數(shù)據(jù)加載之后載開啟外鍵約束：

postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=#

這里看起來可能有點奇怪，但是它的確禁用了外鍵約束。如果有其他外鍵約束，當然也是被禁用了。

我們再來看看表t2：

postgres=# \d t2
         Table "public.t2"
 Column | Type  | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a   | integer |      | not null |
 b   | integer |      |     |
 c   | text  |      |     |
Indexes:
  "t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
  "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
Disabled internal triggers:
  "RI_ConstraintTrigger_c_75213" AFTER INSERT ON t2 FROM t1 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"()
  "RI_ConstraintTrigger_c_75214" AFTER UPDATE ON t2 FROM t1 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_upd"()
 
postgres=#

關鍵字all將表上的其他內(nèi)部觸發(fā)器也禁用了，需要superser才可以執(zhí)行成功。

postgres=# create user abce with login password 'abce';
CREATE ROLE
postgres=# \c postgres abce
You are now connected to database "postgres" as user "abce".
postgres=> create table t3 ( a int primary key, b text, c date);
CREATE TABLE
postgres=> create table t4 ( a int primary key, b int references t3(a), c text);
CREATE TABLE
postgres=> alter table t4 disable trigger all;
ERROR: permission denied: "RI_ConstraintTrigger_c_75235" is a system trigger
postgres=>

那作為普通用戶，該如何禁用觸發(fā)器呢？

postgres=> alter table t4 disable trigger user;

具體語法為：

DISABLE TRIGGER [ trigger_name | ALL | USER ]

回到t1、t2表。

postgres=# select * from t1;
 a | b |   c   
---+----+------------
 1 | aa | 2020-11-04
 2 | bb | 2020-11-04
(2 rows)
 
postgres=# select * from t2;
 a | b | c 
---+---+----
 1 | 1 | aa
 2 | 2 | aa
(2 rows)
 
postgres=# insert into t2 (a,b,c) values (3,3,'cc');
INSERT 0 1
postgres=#

這里插入了一條在t1中不匹配的記錄，但是插入成功了。

postgres=# alter table t2 enable trigger all;
ALTER TABLE
postgres=# \d t2
         Table "public.t2"
 Column | Type  | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a   | integer |      | not null |
 b   | integer |      |     |
 c   | text  |      |     |
Indexes:
  "t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
  "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
 
postgres=# alter table t2 validate constraint t2_b_fkey;
ALTER TABLE
postgres=#

是不是很驚訝，PostgreSQL沒有報告不匹配的記錄。為什么呢？

查看一個pg_constraint：

postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname    | t2_b_fkey
connamespace | 2200
contype    | f
condeferrable | f
condeferred  | f
convalidated | t
conrelid   | 75202
contypid   | 0
conindid   | 75200
conparentid  | 0
confrelid   | 75194
confupdtype  | a
confdeltype  | a
confmatchtype | s
conislocal  | t
coninhcount  | 0
connoinherit | t
conkey    | {2}
confkey    | {1}
conpfeqop   | {96}
conppeqop   | {96}
conffeqop   | {96}
conexclop   |
conbin    |
consrc    |
 
postgres=#

convalidated字段的值為t，表明該外鍵約束還是有效的。

哪怕是我們再次將其disable，仍然會顯示是有效的：

postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname    | t2_b_fkey
connamespace | 2200
contype    | f
condeferrable | f
condeferred  | f
convalidated | t
conrelid   | 75202
contypid   | 0
conindid   | 75200
conparentid  | 0
confrelid   | 75194
confupdtype  | a
confdeltype  | a
confmatchtype | s
conislocal  | t
coninhcount  | 0
connoinherit | t
conkey    | {2}
confkey    | {1}
conpfeqop   | {96}
conppeqop   | {96}
conffeqop   | {96}
conexclop   |
conbin    |
consrc    |
 
postgres=#

這表明當我們開啟(enable)內(nèi)部觸發(fā)器的時候，PostgreSQL不會驗證(validate)約束，因此也不會驗證數(shù)據(jù)是否會有沖突，因為外鍵約束的狀態(tài)始終是有效的。

我們需要做的是先將其變成無效的：

postgres=# alter table t2 alter CONSTRAINT t2_b_fkey not valid;
ERROR: ALTER CONSTRAINT statement constraints cannot be marked NOT VALID
## 需要先將外鍵刪掉，然后重建外鍵約束并將其狀態(tài)設置成無效
 
postgres=# alter table t2 drop constraint t2_b_fkey;
ALTER TABLE
postgres=# delete from t2 where a in (3);
DELETE 1
postgres=# alter table t2 add constraint t2_b_fkey foreign key (b) references t1(a) not valid;
ALTER TABLE
postgres=# \d t2
         Table "public.t2"
 Column | Type  | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a   | integer |      | not null |
 b   | integer |      |     |
 c   | text  |      |     |
Indexes:
  "t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
  "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a) NOT VALID

現(xiàn)在，可以看到狀態(tài)是無效的了：

postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname    | t2_b_fkey
connamespace | 2200
contype    | f
condeferrable | f
condeferred  | f
convalidated | f
conrelid   | 75202
contypid   | 0
conindid   | 75200
conparentid  | 0
confrelid   | 75194
confupdtype  | a
confdeltype  | a
confmatchtype | s
conislocal  | t
coninhcount  | 0
connoinherit | t
conkey    | {2}
confkey    | {1}
conpfeqop   | {96}
conppeqop   | {96}
conffeqop   | {96}
conexclop   |
conbin    |
consrc    |
 
postgres=#

繼續(xù)插入數(shù)據(jù)：

postgres=# insert into t2(a,b,c) values (3,3,'cc');
ERROR: insert or update on table "t2" violates foreign key constraint "t2_b_fkey"
DETAIL: Key (b)=(3) is not present in table "t1".
postgres=#

是不是更驚訝了？創(chuàng)建了一個無效的約束，只是通知PostgreSQL

不要掃描整個表去驗證所有的行記錄是否有效。對于新插入或更新的行，仍然會檢查是否滿足約束條件，這就是為什么上面插入失敗了。

我們該怎么做呢？

1.刪除所有的外鍵

2.加載數(shù)據(jù)

3.重新創(chuàng)建外鍵，但是將其狀態(tài)設置成無效的，從而避免掃描整個表。之后，新的數(shù)據(jù)會被驗證了

4.在系統(tǒng)負載低的時候開啟約束驗證(validate the constraints)

另一種方法是：

postgres=# alter table t2 alter constraint t2_b_fkey deferrable;
ALTER TABLE
postgres=# begin;
BEGIN
postgres=# set constraints all deferred;
SET CONSTRAINTS
postgres=# insert into t2 (a,b,c) values (3,3,'cc');
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (4,4,'dd');
INSERT 0 1
postgres=# insert into t1 (a,b,c) values (3,'cc',now());
INSERT 0 1
postgres=# insert into t1 (a,b,c) values (4,'dd',now());
INSERT 0 1
postgres=# commit;
COMMIT

這樣做不好的方面是，在下一次提交時才起作用，因此，你需要將所有的工作放到一個事務中。

本文的關鍵點是，下面的假設將驗證你的數(shù)據(jù)是錯誤的:

postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# insert into t2 (a,b,c) values (5,5,'ee');
INSERT 0 1
postgres=# alter table t2 enable trigger all;
ALTER TABLE
postgres=#

這只會驗證新的數(shù)據(jù)，但是并不保證所有的數(shù)據(jù)都滿足約束：

postgres = # insert into t2 (a,b,c) values (6,6,'ff');
ERROR: insert or update on table "t2" violates foreign key constraint "t2_b_fkey"
DETAIL: Key(b) = (6) is not present in table "t1".
postgres = # select * from t2 where b = 5;
a | b | c
---+---+----
5 | 5 | ee
(1 row)
 
postgres = # select * from t1 where a = 5;
a | b | c
---+---+---
(0 rows)

最終，還有一種方式來解決，直接修改pg_constraint目錄表。但是并建議用戶這么做！

postgres=# delete from t2 where b = 5;
DELETE 1
postgres=# delete from t2 where b = 5;
DELETE 1
postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# insert into t2 values (5,5,'ee');
INSERT 0 1
postgres=# alter table t2 enable trigger all;
ALTER TABLE
postgres=# update pg_constraint set convalidated = false where conname = 't2_b_fkey' and conrelid = 't2'::regclass;
UPDATE 1
postgres=# alter table t2 validate constraint t2_b_fkey;
ERROR: insert or update on table "t2" violates foreign key constraint "t2_b_fkey"
DETAIL: Key (b)=(5) is not present in table "t1".
postgres=#

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章:

PostgreSQL 查看表的主外鍵等約束關系詳解
postgresql 實現(xiàn)字符串分割字段轉列表查詢
postgresql 查詢集合結果用逗號分隔返回字符串處理的操作
postgresql數(shù)據(jù)庫連接數(shù)和狀態(tài)查詢操作
postgresql查詢自動將大寫的名稱轉換為小寫的案例
postgresql數(shù)據(jù)庫使用說明_實現(xiàn)時間范圍查詢
Postgresql 查詢表引用或被引用的外鍵操作

標簽：辛集杭州寶雞溫州濮陽榆林海西昭通

巨人網(wǎng)絡通訊聲明：本文標題《PostgreSQL中enable、disable和validate外鍵約束的實例》，本文關鍵詞 PostgreSQL,中,enable,disable,；如發(fā)現(xiàn)本文內(nèi)容存在版權問題，煩請?zhí)峁┫嚓P信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡，涉及言論、版權與本站無關。