极乐门资源网

PostgreSQL中enable、disable和validate外键约束的实例
数据库 2024/12/22 佚名
2 0
极乐门资源网 Design By www.ioogu.com

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

postgres=# create table t1(a int primary key,b text,c date);
CREATE TABLE
postgres=# create table t2(a int primary key,b int references t1(a),c text);
CREATE TABLE
postgres=# insert into t1 (a,b,c) values(1,'aa',now());
INSERT 0 1
postgres=# insert into t1 (a,b,c) values(2,'bb',now());
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (1,1,'aa');
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (2,2,'aa');
INSERT 0 1
postgres=# \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 TABLE
postgres=#

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

我们再来看看表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 ROLE
postgres=# \c postgres abce
You are now connected to database "postgres" as user "abce".
postgres=> create table t3 ( a int primary key, b text, c date);
CREATE TABLE
postgres=> create table t4 ( a int primary key, b int references t3(a), c text);
CREATE TABLE
postgres=> alter table t4 disable trigger all;
ERROR: permission denied: "RI_ConstraintTrigger_c_75235" is a system trigger
postgres=>

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

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

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

postgres=# alter table t2 enable trigger all;
ALTER TABLE
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=# alter table t2 validate constraint t2_b_fkey;
ALTER TABLE
postgres=#

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

查看一个pg_constraint：

postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
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=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
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=# 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 TABLE
postgres=# delete from t2 where a in (3);
DELETE 1
postgres=# alter table t2 add constraint t2_b_fkey foreign key (b) references t1(a) not valid;
ALTER TABLE
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) NOT VALID

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

postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
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=# 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 TABLE
postgres=# begin;
BEGIN
postgres=# set constraints all deferred;
SET CONSTRAINTS
postgres=# insert into t2 (a,b,c) values (3,3,'cc');
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (4,4,'dd');
INSERT 0 1
postgres=# insert into t1 (a,b,c) values (3,'cc',now());
INSERT 0 1
postgres=# insert into t1 (a,b,c) values (4,'dd',now());
INSERT 0 1
postgres=# commit;
COMMIT

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

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

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

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

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 1
postgres=# delete from t2 where b = 5;
DELETE 1
postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# insert into t2 values (5,5,'ee');
INSERT 0 1
postgres=# alter table t2 enable trigger all;
ALTER TABLE
postgres=# update pg_constraint set convalidated = false where conname = 't2_b_fkey' and conrelid = 't2'::regclass;
UPDATE 1
postgres=# 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,enable,disable,validate外键约束
极乐门资源网 Design By www.ioogu.com
极乐门资源网 免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
极乐门资源网 Design By www.ioogu.com

评论“PostgreSQL中enable、disable和validate外键约束的实例”

暂无PostgreSQL中enable、disable和validate外键约束的实例的评论...
www.ioogu.com 极乐门资源网
139,976影音资源
144,792福利资源
21,817软件资源
631,128技术资源

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
极乐门资源网 Design By www.ioogu.com