如何在事务中短暂破坏引用完整性而不禁用外键约束？

发布于 2024-09-07 12:03:46 字数 438 浏览 3 评论 0原文

我有一个包含 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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

献世佛 2024-09-14 12:03:46

您想要的是“延迟约束”。

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

回复收藏 0 原文

一身骄傲 2024-09-14 12:03:46

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

正如 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

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

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.

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.

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.

回复收藏 0 原文

云归处 2024-09-14 12:03:46

对于此类场景的常见建议是采用可延迟约束。然而，我认为这些情况几乎总是应用程序逻辑或数据模型的失败。例如，如果我们将其作为两个语句执行，则在同一事务中插入子记录和父记录可能会出现问题：

我的测试数据：

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>

错误的做法：

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>

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

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>

您所处的情况是类似的：您想要更新父记录的主键，但不能'因为子记录的存在：并且您无法更新子记录，因为没有父键。 Catch-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>

解决方案再次是在单个语句中执行此操作：

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>

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:

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:

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:

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.

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.

回复收藏 0 原文

尾戒 2024-09-14 12:03:46

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

更一般地说，该表的问题是它缺少主键。回想一下，主键必须满足三件事：

唯一、
非空、
不变的

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

所有这些小规则都是有原因的。这是了解主键规则中“不变”部分的绝佳机会。

分享并享受。

回复收藏 0 原文

就像说晚安 2024-09-14 12:03:46

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

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

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

回复收藏 0 原文

终难遇 2024-09-14 12:03:46

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

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

ID (pk)
PARENT_ID（外键，引用 ID 列 - 使其自引用）

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

ID (pk)
PARENT_ID（外键，引用 ID 列 - 使其自引用）
SURROGATE_KEY（唯一约束）

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

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