2

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

What doesn't Work:

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

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

Hack/Solution that 'works'

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;

Table Definition:

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

PK/UNIQUE INDEX:

   CREATE UNIQUE INDEX SL_PK ON SPOKEN_LANGUAGES    (ID)
Brian
  • 13,249
  • 9
  • 54
  • 80
  • What about simply dropping the index, performing the update and then rebuilding the index? – BobC Feb 02 '17 at 16:12
  • @BobC This was definitely a suggestion that came up - it seemed the application developers were reluctant to adopt that strategy. I also suggested we refactor the schema and/or application to support what it is they are trying to accomplish; rather than trying to 'fix it' as is. – Brian Feb 02 '17 at 17:07
  • Is this a one time fix, or some sort of regular maintenance? If it was one off, that I don't see a big problem with drop and rebuild the index. Having said that, @a_hourse came up with the right answer anyway. But as you say, and application the relies on that ordering is likely to run into other issues later... – BobC Feb 03 '17 at 05:02

1 Answers1

8

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);
a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
  • Hi @a-horse-with-no-name I have similar situation, with https://stackoverflow.com/q/60288050/1410223 but, some variable registers. Can you help me please? – Anita Feb 18 '20 at 21:08