PostgreSQL中enable、disable和validate外键约束的实例

我就废话不多说了，大家还是直接看实例吧~

postgres=# create table t1(a int Primary key,b text,c date);
    CREATE TABLEpostgres=# create table t2(a int PRimary key,b int references t1(a),c text);
    CREATE TABLEpostgres=# insert into t1 (a,b,c) values(1,'aa',now());
    INSERT 0 1postgres=# insert into t1 (a,b,c) values(2,'bb',now());
    INSERT 0 1postgres=# insert into t2 (a,b,c) values (1,1,'aa');
    INSERT 0 1postgres=# insert into t2 (a,b,c) values (2,2,'aa');
    INSERT 0 1postgres=# \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上的外键约束禁用掉，在数据加载之后载开启外键约束：

postgres=# ALTER TABLE t2 disable trigger all;
    ALTER TABLEpostgres=#

这里看起来可能有点奇怪，但是它的确禁用了外键约束。如果有其他外键约束，当然也是被禁用了。

我们再来看看表t2：

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才可以执行成功。

postgres=# create user abce with login password 'abce';
    CREATE ROLEpostgres=# \c postgres abceYou are now connected to database "postgres" as user "abce".postgres=>
     create table t3 ( a int primary key, b text, c date);
    CREATE TABLEpostgres=>
     create table t4 ( a int primary key, b int references t3(a), c text);
    CREATE TABLEpostgres=>
     alter table t4 disable trigger all;
    ERROR: permission denied: "RI_ConstraintTrigger_c_75235" is a System triggerpostgres=>
    

那作为普通用户，该如何禁用触发器呢？

postgres=>
     alter table t4 disable trigger user;
    

具体语法为：

DISABLE TRIGGER [ trigger_name | ALL | USER ]

回到t1、t2表。

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 1postgres=#

这里插入了一条在t1中不匹配的记录，但是插入成功了。

postgres=# alter table t2 enable trigger all;
    ALTER TABLEpostgres=# \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 TABLEpostgres=#

是不是很惊讶，PostgreSQL没有报告不匹配的记录。为什么呢？

查看一个pg_constraint：

postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------conname    | t2_b_fkeyconnamespace | 2200contype    | fcondeferrable | fcondeferred  | fconvalidated | tconrelid   | 75202contypid   | 0conindid   | 75200conparentid  | 0confrelid   | 75194confupdtype  | aconfdeltype  | aconfmatchtype | sconislocal  | tconinhcount  | 0connoinherit | tconkey    | {
2}
confkey    | {
1}
conpfeqop   | {
96}
conppeqop   | {
96}
conffeqop   | {
96}
    conexclop   |conbin    |consrc    | postgres=#

convalidated字段的值为t，表明该外键约束还是有效的。

哪怕是我们再次将其disable，仍然会显示是有效的：

postgres=# alter table t2 disable trigger all;
    ALTER TABLEpostgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------conname    | t2_b_fkeyconnamespace | 2200contype    | fcondeferrable | fcondeferred  | fconvalidated | tconrelid   | 75202contypid   | 0conindid   | 75200conparentid  | 0confrelid   | 75194confupdtype  | aconfdeltype  | aconfmatchtype | sconislocal  | tconinhcount  | 0connoinherit | tconkey    | {
2}
confkey    | {
1}
conpfeqop   | {
96}
conppeqop   | {
96}
conffeqop   | {
96}
    conexclop   |conbin    |consrc    | postgres=#

这表明当我们开启(enable)内部触发器的时候，PostgreSQL不会验证(validate)约束，因此也不会验证数据是否会有冲突，因为外键约束的状态始终是有效的。

我们需要做的是先将其变成无效的：

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 TABLEpostgres=# delete from t2 where a in (3);
    DELETE 1postgres=# alter table t2 add constraint t2_b_fkey foreign key (b) references t1(a) not valid;
    ALTER TABLEpostgres=# \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

现在，可以看到状态是无效的了：

postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------conname    | t2_b_fkeyconnamespace | 2200contype    | fcondeferrable | fcondeferred  | fconvalidated | fconrelid   | 75202contypid   | 0conindid   | 75200conparentid  | 0confrelid   | 75194confupdtype  | aconfdeltype  | aconfmatchtype | sconislocal  | tconinhcount  | 0connoinherit | tconkey    | {
2}
confkey    | {
1}
conpfeqop   | {
96}
conppeqop   | {
96}
conffeqop   | {
96}
    conexclop   |conbin    |consrc    | postgres=#

继续插入数据：

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)

另一种方法是：

postgres=# alter table t2 alter constraint t2_b_fkey deferrable;
    ALTER TABLEpostgres=# begin;
    BEGINpostgres=# set constraints all deferred;
    SET CONSTRAINTSpostgres=# insert into t2 (a,b,c) values (3,3,'cc');
    INSERT 0 1postgres=# insert into t2 (a,b,c) values (4,4,'dd');
    INSERT 0 1postgres=# insert into t1 (a,b,c) values (3,'cc',now());
    INSERT 0 1postgres=# insert into t1 (a,b,c) values (4,'dd',now());
    INSERT 0 1postgres=# commit;
    COMMIT

这样做不好的方面是，在下一次提交时才起作用，因此，你需要将所有的工作放到一个事务中。

本文的关键点是，下面的假设将验证你的数据是错误的:

postgres=# alter table t2 disable trigger all;
    ALTER TABLEpostgres=# insert into t2 (a,b,c) values (5,5,'ee');
    INSERT 0 1postgres=# alter table t2 enable trigger all;
    ALTER TABLEpostgres=#

这只会验证新的数据，但是并不保证所有的数据都满足约束：

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目录表。但是并建议用户这么做！

postgres=# delete from t2 where b = 5;
    DELETE 1postgres=# delete from t2 where b = 5;
    DELETE 1postgres=# alter table t2 disable trigger all;
    ALTER TABLEpostgres=# insert into t2 values (5,5,'ee');
    INSERT 0 1postgres=# alter table t2 enable trigger all;
    ALTER TABLEpostgres=# update pg_constraint set convalidated = false where conname = 't2_b_fkey' and conrelid = 't2'::regclass;
    UPDATE 1postgres=# 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=#

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• PostgreSQL 查看表的主外键等约束关系详解
• postgresql 实现字符串分割字段转列表查询
• postgresql 查询集合结果用逗号分隔返回字符串处理的操作
• postgresql数据库连接数和状态查询操作
• postgresql查询自动将大写的名称转换为小写的案例
• postgresql数据库使用说明_实现时间范围查询
• Postgresql 查询表引用或被引用的外键操作

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！