PostgreSQL约束延迟生效
PostgreSQL约束延迟生效
当我们对一张表上的数据进行操作时,如果该表上有约束存在,那么约束是在什么时候生效的呢?
例如我们在进行数据迁移的时候就会考虑的这个问题,有的迁移工具在迁移的时候必须得将表约束和数据分开迁移,否则会出现数据无法导入的情况,这就是因为约束不能延迟导致的。
那么pg中对于约束的延判有什么原则呢?
- 数据导出时,约束通常是在数据都写入后再创建。避免先创建约束后导入失败。
- 在使用过程中,PG提供了延迟检测约束的功能:
- 允许约束延判,(建表、建约束时可以指定,后续也可以修改)
- 设置延判规则,在语句结束还是在事务结束时判断约束。可以通过修改约束定义(alter table xxx alter constraint),或者直接在事务中设置规则(SET CONSTRAINTS xxx)。
涉及到以下几个参数:
-
DEFERRABLE/NOT DEFERRABLE
这两个选项控制了该约束是否能被延迟。一个不可延迟的约束将在每一次命令后立刻被检查,可延迟约束的检查将被推迟到事务结束时进行。
-
INITIALLY IMMEDIATE/INITIALLY DEFERRED
延迟判断规则,如果该约束是INITIALLY IMMEDIATE,它会在每一个语句之后被检查。这是默认值。如果该约束是INITIALLY DEFERRED,它只会在事务结束时被检查。
通过修改约束定义设置延迟
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
其中action 是以下之一:
ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
在事务中设置延迟规则:
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
1、不允许约束延迟
postgres=# create table emp(id int primary key,dep_id int);
CREATE TABLE
postgres=# create table dep(id int,emp_id int references emp(id));
CREATE TABLE
postgres=# insert into dep values(1,1);
ERROR: insert or update on table "dep" violates foreign key constraint "dep_emp_id_fkey"
DETAIL: Key (emp_id)=(1) is not present in table "emp".
-- 事务中插入也报错
postgres=# begin;
BEGIN
postgres=# insert into dep values(1,1);
ERROR: insert or update on table "dep" violates foreign key constraint "dep_emp_id_fkey"
DETAIL: Key (emp_id)=(1) is not present in table "emp".
postgres=# rollback;
ROLLBACK
2、允许约束延迟,并设置判断规则为事务结束
postgres=# create table loc(id int,emp_id int references emp(id) INITIALLY DEFERRED);
CREATE TABLE
-- 直接插入数据报错
postgres=# insert into loc values(1,1);
ERROR: insert or update on table "loc" violates foreign key constraint "loc_emp_id_fkey"
DETAIL: Key (emp_id)=(1) is not present in table "emp".
-- 在事务中能够插入数据
postgres=# begin;
BEGIN
postgres=# insert into loc values(1,1);
INSERT 0 1
-- 事务结束保报错
postgres=# commit;
ERROR: insert or update on table "loc" violates foreign key constraint "loc_emp_id_fkey"
DETAIL: Key (emp_id)=(1) is not present in table "emp".
3、允许延判,在事务开启后设置事务结束延判
postgres=# alter table loc alter constraint loc_emp_id_fkey deferrable;
ALTER TABLE
postgres=# begin;
BEGIN
postgres=# insert into loc values(1,1);
ERROR: insert or update on table "loc" violates foreign key constraint "loc_emp_id_fkey"
DETAIL: Key (emp_id)=(1) is not present in table "emp".
postgres=# rollback;
ROLLBACK
postgres=#
postgres=# begin;
BEGIN
-- 设置事务结束延判, 所有约束生效
postgres=# set constraints all deferred;
SET CONSTRAINTS
postgres=# insert into loc values(1,1);
INSERT 0 1
postgres=# rollback;
ROLLBACK
参考资料
http://postgres.cn/docs/12/sql-altertable.html
http://postgres.cn/docs/12/sql-set-constraints.html
https://blog.csdn.net/weixin_39540651/article/details/104036315
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】博客园携手 AI 驱动开发工具商 Chat2DB 推出联合终身会员
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步