文章目录
- 实例
- 现象
- 解决方法(延迟校验）
- 操作演示
实例
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,'
现象
-- 如下操作会报错:
update ta set id=2;
update tb set fid=2;
解决方法(延迟校验）
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;
操作演示
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)
