我就废话不多说了,大家还是直接看实例吧~
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