数据集上插入/删除/修改的正确顺序是什么
本文关键字:顺序 是什么 修改 插入 删除 数据集 | 更新日期: 2023-09-27 18:28:33
MSDN声称订单为:
- 子表:删除记录
- 父表:插入、更新和删除记录
- 子表:插入和更新记录
我对此有意见。
示例:ParentTable有两个记录parent1(Id:1)和parent2(Id:2)
ChildTable有一个记录child1(Id:1,ParentId:1)
如果我们更新child1以拥有新的parent2,然后删除parent1。
- 我们在子表中没有要删除的内容
- 我们删除parent1:我们破坏了约束,因为除非我们首先更新它,否则子对象仍然附加到parent1
那么,正确的顺序是什么?MSDN在这个问题上是错误的吗?
我个人的想法是
- 子表:删除记录
- 父表:插入、更新记录
- 子表:插入和更新记录
- 父表:删除记录
但问题是,由于存在潜在的唯一约束,我们必须始终删除表中的记录,然后再添加新的。。。所以我现在没有将我的数据提交到数据库的解决方案。
编辑:谢谢你的回答,但你的角落案例是我的日常案例。。。我选择了禁用约束的丑陋解决方案,然后更新数据库,然后重新启用约束。我仍在寻找更好的解决方案。。
您的SQL产品不支持延迟约束检查吗?
如果没有,你可以试试
删除所有子记录-删除所有父记录-插入所有父记录–插入所有子记录
其中任何UPDATE已被拆分为其组成部分DELETE和INSERT。
这在所有情况下都应该正确工作,但在任何情况下都可能以可接受的速度工作。。。
同样可以证明的是,这是唯一一个在所有情况下都能正确工作的方案,因为:
(a) 父级上的键约束规定父级DELETES必须在父级INSERTS之前,
(b) 子级上的关键约束规定子级DELETES必须在子级INSERTS之前,
(c) FK规定子DELETES必须在父DELETES之前
(d) FK还规定子INSERTS必须跟在父INSERTS 之后
给定的序列是唯一可能满足这4个要求的序列,它还表明,无论怎样,对子级的UPDATE都会使解决方案变得不可能,因为UPDATE意味着"同时"DELETE加INSERT。
您必须考虑到它们的上下文。MS表示
更新数据集中的相关表时,更新以适当的顺序减少违反引用的机会完整性约束。
在编写客户端数据应用软件的上下文中。
为什么减少违反引用完整性约束的机会很重要?因为违反这些限制意味着
- dbms和客户端之间的往返次数更多,要么是客户端代码处理约束冲突,要么是人工用户处理冲突
- 花费更多的时间
- 服务器上的负载更大
- 更多人为失误的机会,以及
- 并发更新更改底层数据的机会更多(可能混淆应用程序代码、人类用户或两者)
为什么他们认为他们的程序是正确的?因为它提供了一个单一的过程,可以避免在几乎所有常见情况下,甚至在许多不常见的情况下,引用完整性受到侵犯。例如
-
如果更新是对引用表的DELETE操作,并且引用表中的外键被声明为on DELETE CASCADE,那么最好的方法是简单地删除引用行(父行),并让dbms管理级联。(这也是ON DELETE SET DEFAULT和ON DELETE SET NULL的最佳选择。)
-
如果更新是对被引用表的DELETE操作,并且引用表中的外键被声明为on DELETE RESTRICT,那么最好是先删除所有引用行(子行),然后删除被引用行。
但是,在正确使用事务的情况下,不管怎样,MS的过程都会使数据库处于一致状态。其价值在于,它是一个单独的客户端过程,用于编码和维护,尽管并非在所有情况下都是最佳的。(软件设计中经常出现这种情况——选择一种并非在所有情况下都是最优的方式。ActiveRecord跃然纸上。)
你说
示例:ParentTable有两个记录parent1(Id:1)和parent2(Id:2)
ChildTable有一个记录child1(Id:1,ParentId:1)
如果我们更新child1以拥有新的parent2,并且删除parent1。
- 我们在子表中没有要删除的内容
- 我们删除parent1:我们破坏了约束,因为除非我们首先更新它,否则子对象仍然附加到parent1
这不是引用完整性问题;这是一个程序问题。这个问题显然需要两个事务。
-
更新子项以拥有新的父项,然后提交。无论第一个父对象发生了什么,都必须更正这些数据。具体来说,即使存在并发更新或其他限制,使得暂时或永久不可能删除第一个父级,也必须更正这些数据。(这不是引用完整性问题,因为SQL外键约束中没有ON DELETE SET TO NEXT PARENT ID OR MAKE YOUR BEST GUESS子句。)
-
删除第一个父级,然后提交。这可能需要首先更新任意数量表中的任意数量的子行。在一个庞大的组织中,我可以想象一些像这样的删除需要数周才能完成。
听起来像:
- 插入parent2。子对象仍然指向父对象1
- 更新子对象以指向parent2。现在没有引用parent1
- 删除parent1
您可能希望在可用的情况下将其封装在事务中。
根据您的模式,您也可以将其扩展为:
- 更新parent1以指示它已锁定(或将其锁定在DB中),从而阻止更新
- 插入父项2
- 更新子对象以指向父对象2
- 删除父项1
此顺序的优点是,父级和子级之间的联接将始终返回一致的结果。当子级更新时,联接的结果将"翻转"到新状态。
编辑:
另一种选择是将父/子引用移到另一个表中,例如"链接";
CREATE TABLE links (
link_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
parent_id INT NOT NULL,
child_id INT NOT NULL
);
您可能希望在父列和子列上使用外键约束,当然还有一些适当的索引。这种安排允许父表和子表之间的关系非常灵活——可能过于灵活,但这取决于您的应用程序。现在你可以做一些类似的事情;
UPDATE links
SET parent_id = @new_parent_id
WHERE parent_id = @old_parent_id
AND child_id = @child_id;
在不删除子记录的情况下删除父记录的需要非常罕见,我确信MS定义的数据集操作的正常规定顺序不适用于这种情况。
最有效的方法是更新子记录以反映新的父记录,然后删除原始父记录。正如其他人所提到的,此操作应该在事务中执行。
我认为在表上分离操作不是一个好的设计,所以我的解决方案是
- 插入/更新/删除父表
- 插入/更新/删除子表
关键是您不应该更改子记录的parentId,您应该删除parent1的子记录,并向parent2添加一个新的子记录。通过这样做,您将不再担心打破约束。当然,您必须使用transaction。
MSDN声明在使用依赖项(外键)的基础上是正确的。将订单视为
- 子表(级联删除)
- 父表:插入和/或更新和/或删除记录,表示级联删除的最后一步
- 子表:插入或更新
既然我们谈论级联删除,我们必须保证通过删除父记录,在删除父记录之前,需要删除与父记录相关的任何子记录。如果我们没有子记录,则不会在子级别进行删除。仅此而已。
另一方面,你可能会以不同的方式处理你的案件。我认为现实生活中(几乎)的场景会更有帮助。假设父表是订单的主部分(orderID、clientID等),子表是细节部分(detailID、orderID、productOrServiceID等)。所以你收到了一个订单,你有以下
父表
orderID = 1 (auto increment)
...
子表
detailID = 1 (auto increment)
orderID = 1
productOrServiceID = 342
and
detailID = 2
orderID = 1
productOrServiceID = 169
and
detailID = 3
orderID = 1
productOrServiceID = 307
因此,我们有三种产品/服务的一个订单。现在,您的客户希望您将第二个产品或服务转移到新订单中,然后再交付。你有两种选择。
第一个(直接)
创建一个新订单(新的父记录),该订单的订单ID=2
通过设置orderID=2更新子表,其中orderID=1和productOrServiceID=169
因此,您将拥有
父表
orderID = 1 (auto increment)
...
and
orderID = 2
...
子表
detailID = 1 (auto increment)
orderID = 1
productOrServiceID = 342
and
detailID = 2
orderID = 2
productOrServiceID = 169
and
detailID = 3
orderID = 1
productOrServiceID = 307
第二个(间接)
保留子表中第二个产品/服务的DataRow作为变量
从子表中删除相关行
创建一个新订单(新的父记录),该订单的订单ID=2
通过将字段orderID从1更改为2 ,在子表上插入保留的DataRow
因此,您将拥有
父表
orderID = 1 (auto increment)
...
and
orderID = 2
...
子表
detailID = 1 (auto increment)
orderID = 1
productOrServiceID = 342
and
detailID = 3
orderID = 1
productOrServiceID = 307
and
detailID = 4
orderID = 2
productOrServiceID = 169
第二个选项的原因是为每个父记录提供原始的详细ID序列,顺便说一句,它是许多应用程序首选的选项。我看到过通过重新创建所有详细信息记录来扩展第二个选项的案例。我认为很容易找到与此案例相关的开源解决方案并检查实现情况。
最后,我个人的建议是,除非你的应用程序是单用户的,否则不要对数据集做这种事情。数据库可以通过事务以线程安全的方式轻松地处理这个"问题"。