PostgreSQL中enable、disable和validate外键约束的实例
我就废话不多说了，大家还是直接看实例吧~
postgres=#createtablet1(aintprimarykey,btext,cdate); CREATETABLE postgres=#createtablet2(aintprimarykey,bintreferencest1(a),ctext); CREATETABLE postgres=#insertintot1(a,b,c)values(1,'aa',now()); INSERT01 postgres=#insertintot1(a,b,c)values(2,'bb',now()); INSERT01 postgres=#insertintot2(a,b,c)values(1,1,'aa'); INSERT01 postgres=#insertintot2(a,b,c)values(2,2,'aa'); INSERT01 postgres=#\dt1 Table"public.t1" Column|Type|Collation|Nullable|Default --------+---------+-----------+----------+--------- a|integer||notnull| b|text||| c|date||| Indexes: "t1_pkey"PRIMARYKEY,btree(a) Referencedby: TABLE"t2"CONSTRAINT"t2_b_fkey"FOREIGNKEY(b)REFERENCESt1(a) postgres=#\dt2 Table"public.t2" Column|Type|Collation|Nullable|Default --------+---------+-----------+----------+--------- a|integer||notnull| b|integer||| c|text||| Indexes: "t2_pkey"PRIMARYKEY,btree(a) Foreign-keyconstraints: "t2_b_fkey"FOREIGNKEY(b)REFERENCESt1(a) postgres=#
假设我们想通过脚本向表中加载一些数据。因为我们不知道脚本中加载的顺序，我们决定将表t2上的外键约束禁用掉，在数据加载之后载开启外键约束：
postgres=#altertablet2disabletriggerall; ALTERTABLE postgres=#
这里看起来可能有点奇怪，但是它的确禁用了外键约束。如果有其他外键约束，当然也是被禁用了。
我们再来看看表t2：
postgres=#\dt2 Table"public.t2" Column|Type|Collation|Nullable|Default --------+---------+-----------+----------+--------- a|integer||notnull| b|integer||| c|text||| Indexes: "t2_pkey"PRIMARYKEY,btree(a) Foreign-keyconstraints: "t2_b_fkey"FOREIGNKEY(b)REFERENCESt1(a) Disabledinternaltriggers: "RI_ConstraintTrigger_c_75213"AFTERINSERTONt2FROMt1NOTDEFERRABLEINITIALLYIMMEDIATEFOREACHROWEXECUTEPROCEDURE"RI_FKey_check_ins"() "RI_ConstraintTrigger_c_75214"AFTERUPDATEONt2FROMt1NOTDEFERRABLEINITIALLYIMMEDIATEFOREACHROWEXECUTEPROCEDURE"RI_FKey_check_upd"() postgres=#
关键字all将表上的其他内部触发器也禁用了，需要superser才可以执行成功。
postgres=#createuserabcewithloginpassword'abce'; CREATEROLE postgres=#\cpostgresabce Youarenowconnectedtodatabase"postgres"asuser"abce". postgres=>createtablet3(aintprimarykey,btext,cdate); CREATETABLE postgres=>createtablet4(aintprimarykey,bintreferencest3(a),ctext); CREATETABLE postgres=>altertablet4disabletriggerall; ERROR:permissiondenied:"RI_ConstraintTrigger_c_75235"isasystemtrigger postgres=>
那作为普通用户，该如何禁用触发器呢？
postgres=>altertablet4disabletriggeruser;
具体语法为：
DISABLETRIGGER[trigger_name|ALL|USER]
回到t1、t2表。
postgres=#select*fromt1; a|b|c ---+----+------------ 1|aa|2020-11-04 2|bb|2020-11-04 (2rows) postgres=#select*fromt2; a|b|c ---+---+---- 1|1|aa 2|2|aa (2rows) postgres=#insertintot2(a,b,c)values(3,3,'cc'); INSERT01 postgres=#
这里插入了一条在t1中不匹配的记录，但是插入成功了。
postgres=#altertablet2enabletriggerall; ALTERTABLE postgres=#\dt2 Table"public.t2" Column|Type|Collation|Nullable|Default --------+---------+-----------+----------+--------- a|integer||notnull| b|integer||| c|text||| Indexes: "t2_pkey"PRIMARYKEY,btree(a) Foreign-keyconstraints: "t2_b_fkey"FOREIGNKEY(b)REFERENCESt1(a) postgres=#altertablet2validateconstraintt2_b_fkey; ALTERTABLE postgres=#
是不是很惊讶，PostgreSQL没有报告不匹配的记录。为什么呢？
查看一个pg_constraint：
postgres=#select*frompg_constraintwhereconname='t2_b_fkey'andconrelid='t2'::regclass;
-[RECORD1]-+----------
conname|t2_b_fkey
connamespace|2200
contype|f
condeferrable|f
condeferred|f
convalidated|t
conrelid|75202
contypid|0
conindid|75200
conparentid|0
confrelid|75194
confupdtype|a
confdeltype|a
confmatchtype|s
conislocal|t
coninhcount|0
connoinherit|t
conkey|{2}
confkey|{1}
conpfeqop|{96}
conppeqop|{96}
conffeqop|{96}
conexclop|
conbin|
consrc|
postgres=#
convalidated字段的值为t，表明该外键约束还是有效的。
哪怕是我们再次将其disable，仍然会显示是有效的：
postgres=#altertablet2disabletriggerall;
ALTERTABLE
postgres=#select*frompg_constraintwhereconname='t2_b_fkey'andconrelid='t2'::regclass;
-[RECORD1]-+----------
conname|t2_b_fkey
connamespace|2200
contype|f
condeferrable|f
condeferred|f
convalidated|t
conrelid|75202
contypid|0
conindid|75200
conparentid|0
confrelid|75194
confupdtype|a
confdeltype|a
confmatchtype|s
conislocal|t
coninhcount|0
connoinherit|t
conkey|{2}
confkey|{1}
conpfeqop|{96}
conppeqop|{96}
conffeqop|{96}
conexclop|
conbin|
consrc|
postgres=#
这表明当我们开启(enable)内部触发器的时候，PostgreSQL不会验证(validate)约束，因此也不会验证数据是否会有冲突，因为外键约束的状态始终是有效的。
我们需要做的是先将其变成无效的：
postgres=#altertablet2alterCONSTRAINTt2_b_fkeynotvalid; ERROR:ALTERCONSTRAINTstatementconstraintscannotbemarkedNOTVALID ##需要先将外键删掉，然后重建外键约束并将其状态设置成无效 postgres=#altertablet2dropconstraintt2_b_fkey; ALTERTABLE postgres=#deletefromt2whereain(3); DELETE1 postgres=#altertablet2addconstraintt2_b_fkeyforeignkey(b)referencest1(a)notvalid; ALTERTABLE postgres=#\dt2 Table"public.t2" Column|Type|Collation|Nullable|Default --------+---------+-----------+----------+--------- a|integer||notnull| b|integer||| c|text||| Indexes: "t2_pkey"PRIMARYKEY,btree(a) Foreign-keyconstraints: "t2_b_fkey"FOREIGNKEY(b)REFERENCESt1(a)NOTVALID
现在，可以看到状态是无效的了：
postgres=#select*frompg_constraintwhereconname='t2_b_fkey'andconrelid='t2'::regclass;
-[RECORD1]-+----------
conname|t2_b_fkey
connamespace|2200
contype|f
condeferrable|f
condeferred|f
convalidated|f
conrelid|75202
contypid|0
conindid|75200
conparentid|0
confrelid|75194
confupdtype|a
confdeltype|a
confmatchtype|s
conislocal|t
coninhcount|0
connoinherit|t
conkey|{2}
confkey|{1}
conpfeqop|{96}
conppeqop|{96}
conffeqop|{96}
conexclop|
conbin|
consrc|
postgres=#
继续插入数据：
postgres=#insertintot2(a,b,c)values(3,3,'cc'); ERROR:insertorupdateontable"t2"violatesforeignkeyconstraint"t2_b_fkey" DETAIL:Key(b)=(3)isnotpresentintable"t1". postgres=#
是不是更惊讶了？创建了一个无效的约束，只是通知PostgreSQL
不要扫描整个表去验证所有的行记录是否有效。对于新插入或更新的行，仍然会检查是否满足约束条件，这就是为什么上面插入失败了。
我们该怎么做呢？
1.删除所有的外键
2.加载数据
3.重新创建外键，但是将其状态设置成无效的，从而避免扫描整个表。之后，新的数据会被验证了
4.在系统负载低的时候开启约束验证(validatetheconstraints)
另一种方法是：
postgres=#altertablet2alterconstraintt2_b_fkeydeferrable; ALTERTABLE postgres=#begin; BEGIN postgres=#setconstraintsalldeferred; SETCONSTRAINTS postgres=#insertintot2(a,b,c)values(3,3,'cc'); INSERT01 postgres=#insertintot2(a,b,c)values(4,4,'dd'); INSERT01 postgres=#insertintot1(a,b,c)values(3,'cc',now()); INSERT01 postgres=#insertintot1(a,b,c)values(4,'dd',now()); INSERT01 postgres=#commit; COMMIT
这样做不好的方面是，在下一次提交时才起作用，因此，你需要将所有的工作放到一个事务中。
本文的关键点是，下面的假设将验证你的数据是错误的:
postgres=#altertablet2disabletriggerall; ALTERTABLE postgres=#insertintot2(a,b,c)values(5,5,'ee'); INSERT01 postgres=#altertablet2enabletriggerall; ALTERTABLE postgres=#
这只会验证新的数据，但是并不保证所有的数据都满足约束：
postgres=#insertintot2(a,b,c)values(6,6,'ff'); ERROR:insertorupdateontable"t2"violatesforeignkeyconstraint"t2_b_fkey" DETAIL:Key(b)=(6)isnotpresentintable"t1". postgres=#select*fromt2whereb=5; a|b|c ---+---+---- 5|5|ee (1row) postgres=#select*fromt1wherea=5; a|b|c ---+---+--- (0rows)
最终，还有一种方式来解决，直接修改pg_constraint目录表。但是并建议用户这么做！
postgres=#deletefromt2whereb=5; DELETE1 postgres=#deletefromt2whereb=5; DELETE1 postgres=#altertablet2disabletriggerall; ALTERTABLE postgres=#insertintot2values(5,5,'ee'); INSERT01 postgres=#altertablet2enabletriggerall; ALTERTABLE postgres=#updatepg_constraintsetconvalidated=falsewhereconname='t2_b_fkey'andconrelid='t2'::regclass; UPDATE1 postgres=#altertablet2validateconstraintt2_b_fkey; ERROR:insertorupdateontable"t2"violatesforeignkeyconstraint"t2_b_fkey" DETAIL:Key(b)=(5)isnotpresentintable"t1". postgres=#
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。