作者
digoal
日期
2019-11-28
标签
PostgreSQL , 约束延判(unique, primary key, foreign key, exclude)
背景
当表有约束时，约束是在什么时候检测的，是在语句结束，还是insert tuple时，能不能延迟检测，能延迟到语句结束还是事务结束？
如果不能延迟，那么我们在进行数据导入时会非常麻烦，必须先倒入依赖数据，然后再倒入相关数据，如果是环状相互依赖，那么会更麻烦。
PG为了解决约束判断，依赖问题，通常是这样的：
1、数据导出时，约束通常是在数据都写入后再创建。避免先创建约束后倒入失败。
2、在使用过程中，PG提供了延迟检测约束的功能。
2.1、允许约束延判，（建表、建约束时可以指定，后续也可以修改）
2.2、设置延判规则，在语句结束还是在事务结束时判断约束。可以通过修改约束定义，或者直接在事务中设置规则。
涉及语法：
https://www.postgresql.org/docs/12/sql-createtable.html
约束是否允许延迟判断：
DEFERRABLE
NOT DEFERRABLE
This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. Note that deferrable constraints cannot be used as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause.
延迟判断规则：语句结束 or 事务结束
INITIALLY IMMEDIATE
INITIALLY DEFERRED
If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.
在事务中设置约束延迟判断规则：
https://www.postgresql.org/docs/12/sql-set-constraints.html
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
例子
1、不允许延判
```
postgres=# create table pr(id int primary key, c1 int);
CREATE TABLE
postgres=# create table fp(id int references pr(id), c1 int);
CREATE TABLE
postgres=# insert into fp values(1,1);
ERROR: insert or update on table "fp" violates foreign key constraint "fp_id_fkey"
DETAIL: Key (id)=(1) is not present in table "pr".
```
2、允许延判，并设置延判规则为事务结束
```
create table fp(id int references pr(id) INITIALLY DEFERRED, c1 int);
postgres=# begin;
BEGIN
postgres=# insert into fp values(1,1);
INSERT 0 1
```
3、允许延判，在事务开启后设置事务结束延判
```
postgres=# alter table fp alter CONSTRAINT fp_id_fkey deferrable;
ALTER TABLE
postgres=# begin;
BEGIN
postgres=# insert into fp values(1,1);
ERROR: insert or update on table "fp" violates foreign key constraint "fp_id_fkey"
DETAIL: Key (id)=(1) is not present in table "pr".
postgres=# rollback;
ROLLBACK
postgres=# begin;
BEGIN
设置事务结束延判, 所有约束生效
postgres=# set constraints all deferred;
SET CONSTRAINTS
postgres=# insert into fp values(1,1);
INSERT 0 1
postgres=# rollback;
ROLLBACK
```
参考
https://www.postgresql.org/docs/12/sql-createtable.html
https://www.postgresql.org/docs/12/sql-set-constraints.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
