PostgreSQL中enable、disable和validate外鍵約束的實例
更新時間：2021年01月12日 09:45:33 作者：abce
這篇文章主要介紹了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自定義函數(shù)詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
使用python-slim鏡像遇到無法使用PostgreSQL的問題及解決方法
這篇文章主要介紹了使用python-slim鏡像遇到無法使用PostgreSQL的問題及解決方法,本文給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2024-08-08
教你如何在Centos8-stream安裝PostgreSQL13
這篇文章主要介紹了Centos8-stream安裝PostgreSQL13,初始化PostgreSQL需要先創(chuàng)建postgresql儲存目錄，啟動postgresql數(shù)據(jù)庫，本文給大家介紹的非常詳細，需要的朋友可以參考下2022-02-02
PostgreSQL存儲過程循環(huán)調(diào)用方式
這篇文章主要介紹了PostgreSQL存儲過程循環(huán)調(diào)用方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgreSQL 使用timestamp轉成date格式
這篇文章主要介紹了postgreSQL 使用timestamp轉成date格式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
SQL Server數(shù)據(jù)遷移至PostgreSQL出錯的解釋以及解決方案
最近對SQL Server到PostgreSQL的數(shù)據(jù)遷移時出現(xiàn)了問題，返回的錯誤為：invalid byte sequence for encoding "UTF8": 0x00。經(jīng)查證pg源代碼，該問題引起的原因是sql server的字符類型字段中含有空字符\0，該字符在pg中不支持。2014-09-09
PostgreSQL實時查看數(shù)據(jù)庫實例正在執(zhí)行的SQL語句實例詳解
在任何數(shù)據(jù)庫中,分析和優(yōu)化SQL的執(zhí)行,最重要的工作就是執(zhí)行計劃的解讀,而說到執(zhí)行計劃得先了解postgresql的查詢執(zhí)行過程,下面這篇文章主要給大家介紹了關于PostgreSQL實時查看數(shù)據(jù)庫實例正在執(zhí)行的SQL語句的相關資料,需要的朋友可以參考下2023-01-01
這篇文章主要介紹了如何查看postgres數(shù)據(jù)庫端口操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL Sequence序列的使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
Postgresql數(shù)據(jù)庫SQL字段拼接方法
Postgresql里面內(nèi)置了很多的實用函數(shù),下面這篇文章主要給大家介紹了關于Postgresql數(shù)據(jù)庫SQL字段拼接方法的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2023-11-11