交换两个主键值并绕过 ORA-00001:违反唯一约束的更好方法? [英] A better way to swap two primary key values and circumvent a ORA-00001: unique constraint violated?

问题描述

目标:在不遇到 ORA-00001:违反唯一约束的情况下交换两条记录的主键.有效"的解决方案(在下文中)只是一个愚蠢的黑客.是否有一种功能/技术可以将约束强制执行推迟到事务提交?
就动机而言 - 使用此数据的遗留应用程序存在设计缺陷并依赖于 ID 顺序和值 - 请求是按如下方式交换 PK 值:

Goal: Swap the primary keys of two records without encountering an ORA-00001: unique constraint violated. The solution that 'works' (further below) is just a silly hack. Is there a feature/technique to defer constraint enforcement until the transaction is committed?
As far as motivation - a legacy application that uses this data has a design flaw and relies on the IDs order and values - the request is to swap the PK values as follows:

BEFORE: 
388    English
389    French

AFTER:
389    English
388    French

什么不起作用:

BEGIN
   UPDATE SPOKEN_LANGUAGES
      SET id = 388
    WHERE id = 389;

   UPDATE SPOKEN_LANGUAGES
      SET id = 389
    WHERE id = 388;
END;

有效"的黑客/解决方案

DECLARE
  V_MAGIC_NUMBER   NUMBER := 9999999;
BEGIN
  UPDATE SPOKEN_LANGUAGES
     SET id = 388 + V_MAGIC_NUMBER
   WHERE id = 389;

  UPDATE SPOKEN_LANGUAGES
     SET id = 389 + V_MAGIC_NUMBER
   WHERE id = 388;

  UPDATE SPOKEN_LANGUAGES
     SET id = id - V_MAGIC_NUMBER
   WHERE id = 389 + V_MAGIC_NUMBER;

  UPDATE SPOKEN_LANGUAGES
     SET id = id - V_MAGIC_NUMBER
   WHERE id = 388 + V_MAGIC_NUMBER;

END;

表格定义:

CREATE TABLE SPOKEN_LANGUAGES
(
  ID             NUMBER(10)                     NOT NULL,
  LANGUAGE_NAME  VARCHAR2(40 BYTE)              NOT NULL
)

PK/唯一索引:

   CREATE UNIQUE INDEX SL_PK ON SPOKEN_LANGUAGES    (ID)

推荐答案

你需要在一个语句中完成:

You need to do it in a single statement:

UPDATE SPOKEN_LANGUAGES
   SET id = case when id = 388 then 389 else 388 end
WHERE id in (388,389);

这篇关于交换两个主键值并绕过 ORA-00001:违反唯一约束的更好方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！