我就废话不多说了，大家还是直接看实例吧~
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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=# |
假设我们想通过脚本向表中加载一些数据。因为我们不知道脚本中加载的顺序，我们决定将表t2上的外键约束禁用掉，在数据加载之后载开启外键约束：
|
1 2 3 |
postgres=# alter table t2 disable trigger all ; ALTER TABLE postgres=# |
这里看起来可能有点奇怪，但是它的确禁用了外键约束。如果有其他外键约束，当然也是被禁用了。
我们再来看看表t2：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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将表上的其他内部触发器也禁用了，需要superser才可以执行成功。
|
1 2 3 4 5 6 7 8 9 10 11 |
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=> |
那作为普通用户，该如何禁用触发器呢？
|
1 |
postgres=> alter table t4 disable trigger user ; |
具体语法为：
|
1 |
DISABLE TRIGGER [ trigger_name | ALL | USER ] |
回到t1、t2表。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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中不匹配的记录，但是插入成功了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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，仍然会显示是有效的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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)内部触发器的时候，PostgreSQL不会验证(validate)约束，因此也不会验证数据是否会有冲突，因为外键约束的状态始终是有效的。
我们需要做的是先将其变成无效的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
postgres=# alter table t2 alter CONSTRAINT t2_b_fkey not valid; ERROR: ALTER CONSTRAINT statement constraints cannot be marked NOT VALID ## 需要先将外键删掉，然后重建外键约束并将其状态设置成无效
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 |
现在，可以看到状态是无效的了：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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=# |
继续插入数据：
|
1 2 3 4 |
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=# |
是不是更惊讶了？创建了一个无效的约束，只是通知PostgreSQL
不要扫描整个表去验证所有的行记录是否有效。对于新插入或更新的行，仍然会检查是否满足约束条件，这就是为什么上面插入失败了。
我们该怎么做呢？
1.删除所有的外键
2.加载数据
3.重新创建外键，但是将其状态设置成无效的，从而避免扫描整个表。之后，新的数据会被验证了
4.在系统负载低的时候开启约束验证(validate the constraints)
另一种方法是：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
这样做不好的方面是，在下一次提交时才起作用，因此，你需要将所有的工作放到一个事务中。
本文的关键点是，下面的假设将验证你的数据是错误的:
|
1 2 3 4 5 6 7 |
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=# |
这只会验证新的数据，但是并不保证所有的数据都满足约束：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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目录表。但是并建议用户这么做！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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=# |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://www.cnblogs.com/abclife/p/13925374.html
查看更多关于PostgreSQL中enable、disable和validate外键约束的实例的详细内容...