如何在事务中短暂破坏引用完整性而不禁用外键约束?
我有一个包含 3 列的表:
ID, PARENT_ID, NAME
PARENT_ID
与同一个表中的 ID
具有外键关系。该表正在对层次结构进行建模。
有时记录的ID
会发生变化。我希望能够更新记录的 ID
,然后更新依赖记录的 PARENT_ID
以指向新的 ID
。
问题是,当我尝试更新记录的 ID 时,它会破坏完整性并立即失败。
我意识到我可以使用新的 ID
插入一条新记录,然后更新子记录,然后删除旧记录,但是我们有很多触发器,如果我这样做的话就会搞砸。
有没有办法暂时更新父级并承诺更新子级(显然它会在提交时失败)而无需短暂禁用外键?
I have a table with 3 columns:
ID, PARENT_ID, NAME
PARENT_ID
has a foreign key relationship with ID
in the same table. This table is modeling a hierarchy.
Sometimes the ID
of a record will change. I want to be able to update a record's ID
, then update the dependent records' PARENT_ID
to point to the new ID
.
The problem is, when I attempt to update the ID
of a record it breaks the integrity and fails immediately.
I realize I could insert a new record with the new ID
, then update the children, then delete the old record, but we have a lot of triggers in place that would get screwed up if I did that.
Is there any way to temporarily update the parent with the promise of updating the children (obviously it would fail on commit) without disabling the foreign key briefly?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
您想要的是“延迟约束”。
您可以在两种类型的可延迟约束“INITIALLY IMMEDIATE”和“INITIALLY DEFERRED”之间进行选择以驱动默认行为 - 数据库是否应默认在每个语句后检查约束,或者是否应默认仅在最后检查约束交易的。
What you want is a 'deferred constraint'.
You can pick between the two types of deferrable constraints, 'INITIALLY IMMEDIATE' and 'INITIALLY DEFERRED' to drive default behavior - whether the database should default to check the constraint after every statement, or if it should default to only checking constraints at the end of the transaction.
回答比 Chi 慢,但觉得包含代码示例会很好,这样就可以在 SO 上找到答案。
正如 Chi 所回答的,可延期的约束使这成为可能。
我相信,但找不到参考,可延迟性是在约束创建时定义的,以后不能修改。默认是不可推迟的。要更改为可延迟约束,您需要执行一次性删除并添加约束。 (适当安排、控制等)
Answered slower than Chi, but felt it would be nice to include code sample, so that the answer could be found on SO.
As Chi answered, deferrable constraints make this possible.
I believe, but could not find the reference, that deferrability is defined at constraint creation time and can not be modified later. The default is non-deferrable. To change to deferrable constraints you'll need to do a one time drop and add constraint. (Properly scheduled, controlled, etc.)
对于此类场景的常见建议是采用 可延迟约束。然而,我认为这些情况几乎总是应用程序逻辑或数据模型的失败。例如,如果我们将其作为两个语句执行,则在同一事务中插入子记录和父记录可能会出现问题:
我的测试数据:
错误的做法:
但是,Oracle 支持多表 INSERT synatx,它允许我们将父记录和子记录插入到同一个语句中,从而避免了可延迟约束的需要:
您所处的情况是类似的:您想要更新父记录的主键,但不能'因为子记录的存在:并且您无法更新子记录,因为没有父键。 Catch-22:
解决方案再次是在单个语句中执行此操作:
UPDATE 语句中的语法有点笨拙,但拼凑通常是这样。重点是我们不应该经常更新主键列。事实上,由于不变性是“主键性”的特征之一,我们根本不需要更新它们。需要这样做是数据模型的失败。避免此类故障的一种方法是使用合成(代理)主键,并通过唯一约束简单地强制自然(也称为业务)键的唯一性。
那么为什么 Oracle 提供可延迟约束呢?当我们进行数据迁移或批量数据上传时,它们非常有用。它们允许我们清理数据库中的数据,而无需暂存表。我们确实不需要它们来执行常规应用程序任务。
The common advice with scenarios like this is to employ deferrable constraints. However, I think these situations are almost always a failure of application logic or data model. For instance, inserting a child record and a parent record in the same transaction can be a problem if we execute it as two statements:
My test data:
The wrong way to do things:
However, Oracle supports a multi-table INSERT synatx which allows us to insert the parent and child records in the same statement, thus obviating the need for deferrable constraints:
The situation you are in is similar: you want to update the primary key of the parent record but can't because of the existence of the child records: And you can't update the child records because there is no parent key. Catch-22:
Once again the solution is to do it in a single statement:
The syntax in the UPDATE statement is a bit clunky but kludges usually are. The point being that we should not have to update primary key columns very often. Indeed, as immutability is one of the characteristics of "primary key-ness" we shouldn't really have to update them at all. Needing to do so is a failure of the data model. One way of avoiding such failures is to use a synthetic (surrogate) primary key, and simply enforce the uniqueness of the natural (aka business) key with a unique constraint.
So why does Oracle offer deferrable constraints? They are useful when we undertake data migrations or bulk data uploads. They permit us to cleanse data in the database without staging tables. We really shouldn't need them for regular application tasks.
IMO,使用代理键的建议非常好。
更一般地说,该表的问题是它缺少主键。回想一下,主键必须满足三件事:
数据库 我熟悉强制执行 (1) 和 (2),但我不相信它们强制执行 (3),这是不幸的。这就是让你恼火的地方 - 如果你更改了“主键”,如果你不想破坏完整性,则必须追踪对该关键字段的所有引用并进行等效的更改。正如其他人所说,解决方案是拥有一个真正的主键 - 唯一、非空且不会更改的主键。
所有这些小规则都是有原因的。这是了解主键规则中“不变”部分的绝佳机会。
分享并享受。
Recommendations to use a surrogate key are excellent, IMO.
More generally, the problem with this table is that it lacks a primary key. Recall that a primary key must be three things:
Databases I'm familiar with enforce (1) and (2), but I don't believe they enforce (3), which is unfortunate. And that's what's kicking you in the butt - if you change your "primary key" you have to chase down all the references to that key field and make equivalent alterations if you don't want to break integrity. The solution, as others have said, is to have a true primary key - one that is unique, non-null, and which doesn't change.
There's reasons for all these little rules. This is a great opportunity to understand the "unchanging" part of the primary key rules.
Share and enjoy.
如果这是除 Oracle 之外的任何其他数据库,您可以使用
ON UPDATE CASCADE
声明外键。然后,如果您更改父级的 id,它会将更改自动传播到子级的parent_id。不幸的是,Oracle 实现了级联删除,但没有实现级联更新。
(此答案仅供参考,因为它实际上并不能解决您的问题。)
If this were any other database besides Oracle, you could declare the foreign key with
ON UPDATE CASCADE
. Then if you change a parent's id, it would propagate the change atomically to the child's parent_id.Unfortunately, Oracle implements cascading deletes but not cascading updates.
(This answer is for information purposes only, since it doesn't actually solve your problem.)
您需要使用可延迟约束(请参阅 Chi 的答案)。
否则,为了添加一个使外键约束失败的值,您必须禁用或删除 &重新创建外键约束。
此类情况采用代理键,用户可以根据需要更改代理键,而不会影响引用完整性。为了扩展这个想法,当前的设置是:
..并且业务规则是 ID 可以更改。从设计角度来看,这从根本上来说是不好的——主键是不可变的、唯一的并且不能为空。因此,在构建数据模型时解决这种情况的方法是使用:
SURROGATE_KEY 是支持更改的列不影响引用完整性 - 父级和父级孩子关系完好无损。这意味着用户可以根据自己的喜好调整代理键,而无需延迟约束、启用/禁用或删除/重新创建外键约束、更新级联...
通常,在数据建模中,您由于此类情况,切勿向用户显示主键值。例如,我有一个客户希望他们的职位编号在年初发生变化,年份位于数字的开头(IE:201000001 将是 2010 年创建的第一个职位)。当客户出售公司而新所有者需要不同的会计方案时会发生什么?或者,如果在转换到不同的数据库供应商时无法维护编号怎么办?
You need to use a deferrable constraint (see Chi's answer).
Otherwise, in order to add a value that will fail the foreign key constraint, you have to either disable or drop & re-create the foreign key constraint.
Situations like these employ a surrogate key that can be altered by users as necessary, without impacting referential integrity. To expand on this idea, currently the setup is:
..and the business rules are that ID can change. Which is fundamentally bad from a design perspective - primary key are immutable, unique, and can't be null. So the solution to the situation when you're building your data model is to use:
The SURROGATE_KEY is the column that supports change without affecting referential integrity - the parent & child relationship is intact. This means that a user can tweak the surrogate key to their hearts delight without needing deferred constraints, enable/disable or drop/recreate foreign key constraints, ON UPDATE CASCADE...
As a rule, in data modeling you NEVER display primary key values to the user because of situations like these. For example, I have a client who wants their jobs number to change on the start of the year, with the year at the start of the number (IE: 201000001 would be the first job created in 2010). What happens when the client sells the company, and the new owner needs a different scheme for their accounting? Or, what if the numbering can't be maintained while transitioning to a different database vendor?