Geek asks and answers

Updating a primary key without triggering a unique key violation

I just came to this very simple situation, when I needed to transfer the primary key to a specific value. Suppose the following table:

CREATE TABLE Test (
 Id INTEGER PRIMARY KEY,
 Desc TEXT);

Loaded with the following values:

INSERT INTO Test VALUES (0,'one');
INSERT INTO Test VALUES (1,'two');

If there is an attempt to update the primary key, it will of course fail:

UPDATE Test SET Id = Id+1;

Error: column id is not unique

Is there a way to pause integrity checking until an update request is completed?

+3
source share
3 answers

Find a good reference point and move the data around this bar. For example, if all your identifiers are positive, a good core is 0.

If you usually do

UPDATE Test SET Id = Id+1;

Do this sequence instead

UPDATE Test SET Id = -Id;
UPDATE Test SET Id = -Id +1;

, .

+7

( , !), , .

update test
set id = id * (select max(id) + 1 from test)

, , , ...

+1

OK Second attempt. Try the following:

  • Get the MAX key column.
  • UPDATE table SET key = key + max + 1
  • UPDATE table SET key = key - max

This will avoid duplicating keys at any time during the upgrade process by moving the window far enough.

0
source

More articles:


All Articles