PG 外键约束之 deferrable_postgres 外键约束 deferrable

实例

drop table if exists ta cascade;
drop table if exists tb cascade;
create table ta(id int primary key, name varchar);
create table tb(id int primary key, fid int , constraint fk_tb_fid foreign key(fid) references ta(id));
insert into ta values(1,'
1
2
3
4
5

现象

-- 如下操作会报错:
update ta set id=2;
update tb set fid=2;
1
2
3

解决方法(延迟校验）

begin;
    alter table tb alter constraint fk_tb_fid DEFERRABLE initially deferred;
    update ta set id=2;
    update tb set fid=2;
commit;
alter table tb alter constraint fk_tb_fid  not DEFERRABLE;
1
2
3
4
5
6

操作演示

postgres=# drop table ta cascade;
NOTICE:  drop cascades to constraint fk_tb_fid on table tb
DROP TABLE
postgres=# 
postgres=# drop table if exists ta cascade;
NOTICE:  table "ta" does not exist, skipping
DROP TABLE
postgres=# drop table if exists tb cascade;
DROP TABLE
postgres=# create table ta(id int primary key, name varchar);
CREATE TABLE
postgres=# create table tb(id int primary key, fid int , constraint fk_tb_fid foreign key(fid) references ta(id));
CREATE TABLE
postgres=# insert into ta values(1,'a');
INSERT 0 1
postgres=# insert into tb values(1,1);
INSERT 0 1
postgres=# select * from ta ;
 id | name 
----+------
  1 | a
(1 row)


postgres=# select * from tb ;
 id | fid 
----+-----
  1 |   1
(1 row)

postgres=# update ta set id=2;
ERROR:  update or delete on table "ta" violates foreign key constraint "fk_tb_fid" on table "tb"
DETAIL:  Key (id)=(1) is still referenced from table "tb".
postgres=# update tb set fid=2;
ERROR:  insert or update on table "tb" violates foreign key constraint "fk_tb_fid"
DETAIL:  Key (fid)=(2) is not present in table "ta".
postgres=# begin;
BEGIN
postgres=#     alter table tb alter constraint fk_tb_fid DEFERRABLE initially deferred;
ALTER TABLE
postgres=#     update ta set id=2;
UPDATE 1
postgres=#     update tb set fid=2;
UPDATE 1
postgres=# commit;
COMMIT
postgres=# alter table tb alter constraint fk_tb_fid  not DEFERRABLE;
ALTER TABLE
postgres=# \d ta
                      Table "public.ta"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | integer           |           | not null | 
 name   | character varying |           |          | 
Indexes:
    "ta_pkey" PRIMARY KEY, btree (id)
 Referenced by:
    TABLE "tb" CONSTRAINT "fk_tb_fid" FOREIGN KEY (fid) REFERENCES ta(id)

postgres=# \d tb
                 Table "public.tb"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 fid    | integer |           |          | 
Indexes:
"tb_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_tb_fid" FOREIGN KEY (fid) REFERENCES ta(id)

postgres=# select * from ta;
 id | name 
----+------
  2 | a
(1 row)
postgres=# select * from tb;
 id | fid 
----+-----
  1 |   2
(1 row)
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80