oracle 如何在不禁用外键约束的情况下，在事务中简单地破坏参照完整性？

How can I break referential integrity briefly, within a transaction, without disabling the foreign key constraint?

提问by aw crud

I have a table with 3 columns:

我有一个包含 3 列的表：

ID, PARENT_ID, NAME

PARENT_IDhas a foreign key relationship with IDin the same table. This table is modeling a hierarchy.

PARENT_IDID在同一个表中有外键关系。该表正在对层次结构进行建模。

Sometimes the IDof a record will change. I want to be able to update a record's ID, then update the dependent records' PARENT_IDto point to the new ID.

有时ID记录会改变。我希望能够更新记录的ID，然后更新相关记录PARENT_ID以指向新的ID.

The problem is, when I attempt to update the IDof a record it breaks the integrity and fails immediately.

问题是，当我尝试更新ID记录时，它会破坏完整性并立即失败。

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.

我意识到我可以使用 new 插入一条新记录ID，然后更新子项，然后删除旧记录，但是我们有很多触发器，如果​​我这样做会搞砸。

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?

有没有办法临时更新父级并承诺更新子级（显然它会在提交时失败）而不会短暂禁用外键？

回答by Chi

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.

您可以在两种类型的可延迟约束之间进行选择，“INITIALLY IMMEDIATE”和“INITIALLY DEFERRED”来驱动默认行为 - 数据库是否应该默认在每个语句之后检查约束，或者是否应该默认只在最后检查约束的交易。

回答by Shannon Severance

Answered slower than Chi, but felt it would be nice to include code sample, so that the answer could be found on SO.

回答比 Chi 慢，但觉得包含代码示例会很好，以便可以在 SO 上找到答案。

As Chi answered, deferrable constraints make this possible.

正如 Chi 回答的那样，可延迟约束使这成为可能。

SQL> drop table t;

Table dropped.

SQL> create table T (ID number
  2      , parent_ID number null
  3      , name varchar2(40) not null
  4      , constraint T_PK primary key (ID)
  5      , constraint T_HIREARCHY_FK foreign key (parent_ID)
  6          references T(ID) deferrable initially immediate);

Table created.

SQL> insert into T values (1, null, 'Big Boss');

1 row created.

SQL> insert into T values (2, 1, 'Worker Bee');

1 row created.

SQL> commit;

Commit complete.

SQL> -- Since initially immediate, the following statement will fail:
SQL> update T
  2  set ID = 1000
  3  where ID = 1;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint (S.T_HIREARCHY_FK) violated - child record found


SQL> set constraints all deferred;

Constraint set.

SQL> update T
  2  set ID = 1000
  3  where ID = 1;

1 row updated.

SQL> update T
  2  set parent_ID = 1000
  3  where parent_ID = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from T;

        ID  PARENT_ID NAME
---------- ---------- ----------------------------------------
      1000            Big Boss
         2       1000 Worker Bee

SQL> -- set constraints all deferred during that transaction
SQL> -- and the transaction has commited, the next
SQL> -- statement will fail
SQL> update T
  2  set ID = 1
  3  where ID = 1000;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint S.T_HIREARCHY_FK) violated - child record found

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.)

我相信，但找不到参考，可延迟性是在约束创建时定义的，以后无法修改。默认是不可延期的。要更改为可延迟约束，您需要执行一次性删除和添加约束。（适当安排、控制等）

SQL> drop table t;

Table dropped.

SQL> create table T (ID number
  2      , parent_ID number null
  3      , name varchar2(40) not null
  4      , constraint T_PK primary key (ID)
  5      , constraint T_HIREARCHY_FK foreign key (parent_ID)
  6          references T(ID));

Table created.

SQL> alter table T drop constraint T_HIREARCHY_FK;

Table altered.

SQL> alter table T add constraint T_HIREARCHY_FK foreign key (parent_ID)
  2      references T(ID) deferrable initially deferred;

Table altered.

回答by APC

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:

我的测试数据：

SQL> select * from t23 order by id, parent_id
  2  /

        ID  PARENT_ID NAME
---------- ---------- ------------------------------
       110            parent 1
       111            parent 2
       210        110 child 0
       220        111 child 1
       221        111 child 2
       222        111 child 3

6 rows selected.

SQL>

The wrong way to do things:

错误的做事方式：

SQL> insert into t23 (id, parent_id, name) values (444, 333, 'new child')
  2  /
insert into t23 (id, parent_id, name) values (444, 333, 'new child')
*
ERROR at line 1:
ORA-02291: integrity constraint (APC.T23_T23_FK) violated - parent key not
found


SQL> insert into t23 (id, parent_id, name) values (333, null, 'new parent')
  2  /

1 row created.

SQL>

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:

但是，Oracle 支持多表 INSERT 语法，它允许我们在同一语句中插入父记录和子记录，从而避免了对延迟约束的需要：

SQL> rollback
  2  /

Rollback complete.

SQL> insert all
  2      into t23 (id, parent_id, name)
  3          values (child_id, parent_id, child_name)
  4      into t23 (id, name)
  5          values (parent_id, parent_name)
  6  select  333 as parent_id
  7          , 'new parent' as parent_name
  8          , 444 as child_id
  9          , 'new child' as child_name
 10  from dual
 11  /

2 rows created.

SQL>

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:

你的情况是类似的：你想更新父记录的主键，但是因为子记录的存在而不能： 并且你不能更新子记录，因为没有父键。第 22 条军规：

SQL> update t23
  2      set id = 555
  3  where id = 111
  4  /
update t23
*
ERROR at line 1:
ORA-02292: integrity constraint (APC.T23_T23_FK) violated - child record found


SQL> update t23
  2      set parent_id = 555
  3  where parent_id = 111
  4  /
update t23
*
ERROR at line 1:
ORA-02291: integrity constraint (APC.T23_T23_FK) violated - parent key not
found


SQL>

Once again the solution is to do it in a single statement:

再一次，解决方案是在单个语句中完成：

SQL> update t23
  2      set id = decode(id, 111, 555, id)
  3          , parent_id = decode(parent_id, 111, 555, parent_id)
  4  where id = 111
  5     or parent_id = 111
  6  /

4 rows updated.

SQL> select * from t23 order by id, parent_id
  2  /

        ID  PARENT_ID NAME
---------- ---------- ------------------------------
       110            parent 1
       210        110 child 0
       220        555 child 1
       221        555 child 2
       222        555 child 3
       333            new parent
       444        333 new child
       555            parent 2

8 rows selected.

SQL>

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.

UPDATE 语句中的语法有点笨拙，但通常是杂乱无章的。关键是我们不应该经常更新主键列。事实上，由于不变性是“主键性”的特征之一，我们根本不应该真正更新它们。需要这样做是数据模型的失败。避免此类失败的一种方法是使用合成（代理）主键，并通过唯一约束简单地强制执行自然（又名业务）键的唯一性。

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.

那么为什么 Oracle 提供可延迟约束呢？当我们进行数据迁移或批量数据上传时，它们很有用。它们允许我们在不使用暂存表的情况下清理数据库中的数据。我们真的不应该在常规应用程序任务中需要它们。

回答by Bob Jarvis - Reinstate Monica

Recommendations to use a surrogate key are excellent, IMO.

使用代理键的建议非常好，IMO。

More generally, the problem with this table is that it lacks a primary key. Recall that a primary key must be three things:

更一般地说，这个表的问题在于它缺少主键。回想一下，主键必须是三样东西：

1. Unique
2. Non-null
3. Unchanging

1. 独特的
2. 非空
3. 不变

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.

我熟悉的数据库强制执行 (1) 和 (2)，但我不相信它们强制执行 (3)，这是不幸的。这就是让您大吃一惊的原因——如果您更改“主键”，您必须查找对该键字段的所有引用，如果您不想破坏完整性，则必须进行等效更改。正如其他人所说，解决方案是拥有一个真正的主键——一个唯一的、非空的，并且不会改变。

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.

分享和享受。

回答by Bill Karwin

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.

如果这是除 Oracle 之外的任何其他数据库，您可以使用ON UPDATE CASCADE. 然后，如果您更改父级的 id，它会将更改以原子方式传播到子级的 parent_id。

Unfortunately, Oracle implements cascading deletes but notcascading updates.

不幸的是，Oracle 实现了级联删除而不是级联更新。

(This answer is for information purposes only, since it doesn't actually solve your problem.)

（此答案仅供参考，因为它实际上并不能解决您的问题。）

回答by OMG Ponies

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.

您需要使用可延迟约束（请参阅 Chi 的回答）。
否则，为了添加一个会使外键约束失败的值，您必须禁用或删除并重新创建外键约束。

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:

此类情况使用代理键，用户可以根据需要更改该键，而不会影响参照完整性。为了扩展这个想法，目前的设置是：

• ID (pk)
• PARENT_ID (foreign key, references ID column -- making it self referential)

• 身（包）
• PARENT_ID（外键，引用 ID 列——使其自引用）

..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:

..并且业务规则是 ID 可以更改。从设计的角度来看，这从根本上是不好的 - 主键是不可变的、唯一的，并且不能为空。因此，当您构建数据模型时，解决方案是使用：

• ID (pk)
• PARENT_ID (foreign key, references ID column -- making it self referential)
• SURROGATE_KEY (unique constraint)

• 身（包）
• PARENT_ID（外键，引用 ID 列——使其自引用）
• SURROGATE_KEY（唯一约束）

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...

SURROGATE_KEY 是支持更改而不影响参照完整性的列 - 父子关系完好无损。这意味着用户可以根据自己的喜好调整代理键，而无需延迟约束，启用/禁用或删除/重新创建外键约束，ON UPDATE CASCADE ...

As a rule, in data modeling you NEVERdisplay 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?

通常，在数据建模中，由于此类情况，您永远不会向用户显示主键值。例如，我有一个客户希望他们的工作编号在年初更改，年份位于编号的开头（即：201000001 将是 2010 年创建的第一个工作）。当客户出售公司而新所有者需要不同的会计方案时会发生什么？或者，如果在转换到不同的数据库供应商时无法保持编号怎么办？