Замена уникальных индексированных значений столбцов в базе данных

Волшебное слово здесь DEFERRABLE :

DROP TABLE ztable CASCADE;
CREATE TABLE ztable
    ( id integer NOT NULL PRIMARY KEY
    , payload varchar
    );
INSERT INTO ztable(id,payload) VALUES (1,'one' ), (2,'two' ), (3,'three' );
SELECT * FROM ztable;


    -- This works, because there is no constraint
UPDATE ztable t1
SET payload=t2.payload
FROM ztable t2
WHERE t1.id IN (2,3)
AND t2.id IN (2,3)
AND t1.id <> t2.id
    ;
SELECT * FROM ztable;

ALTER TABLE ztable ADD CONSTRAINT OMG_WTF UNIQUE (payload)
    DEFERRABLE INITIALLY DEFERRED
    ;

    -- This should also work, because the constraint 
    -- is deferred until "commit time"
UPDATE ztable t1
SET payload=t2.payload
FROM ztable t2
WHERE t1.id IN (2,3)
AND t2.id IN (2,3)
AND t1.id <> t2.id
    ;
SELECT * FROM ztable;

RESULT:

DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ztable_pkey" for table "ztable"
CREATE TABLE
INSERT 0 3
 id | payload
----+---------
  1 | one
  2 | two
  3 | three
(3 rows)

UPDATE 2
 id | payload
----+---------
  1 | one
  2 | three
  3 | two
(3 rows)

NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "omg_wtf" for table "ztable"
ALTER TABLE
UPDATE 2
 id | payload
----+---------
  1 | one
  2 | two
  3 | three
(3 rows)

Я думаю, что вы должны пойти на решение 2. Ни в одном известном мне варианте SQL нет функции 'swap'.

Если вам нужно делать это регулярно, я предлагаю решение 1, в зависимости от того, как другие части программного обеспечения используют эти данные. У вас могут быть проблемы с блокировкой, если вы не будете осторожны.

Но вкратце: нет никакого другого решения, кроме тех, которые вы предоставили.

Далее следует ответ Энди Ирвинга

это сработало для меня (на SQL Server 2005) в аналогичной ситуации где у меня есть составной ключ, и мне нужно поменять местами поле, которое является частью уникального ограничения.

ключ: pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2

и мне нужно поменять местами LNUM так, чтобы результат был

ключ: pID, LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0

необходим SQL:

UPDATE    DOCDATA    
SET       LNUM = CASE LNUM
              WHEN 0 THEN 1
              WHEN 1 THEN 2 
              WHEN 2 THEN 0 
          END
WHERE     (pID = 10) 
  AND     (LNUM IN (0, 1, 2))

Существует еще один подход, который работает с сервером SQL: используйте временную таблицу, присоединенную к нему в вашем операторе UPDATE.

Проблема вызвана наличием двух строк с одинаковым значением в одно и то же время, но если вы обновите обе строки сразу (до их новых, уникальных значений), нарушение ограничений не произойдет.

Псевдокод:

-- setup initial data values:
insert into data_table(id, name) values(1, 'A')
insert into data_table(id, name) values(2, 'B')

-- create temp table that matches live table
select top 0 * into #tmp_data_table from data_table

-- insert records to be swapped
insert into #tmp_data_table(id, name) values(1, 'B')
insert into #tmp_data_table(id, name) values(2, 'A')

-- update both rows at once! No index violations!
update data_table set name = #tmp_data_table.name
from data_table join #tmp_data_table on (data_table.id = #tmp_data_table.id)

Спасибо Ричу н за эту технику. - Марк

Я также думаю, что #2-это лучший вариант, хотя я бы обязательно завернул его в транзакцию, если что-то пойдет не так в середине обновления.

Альтернативой (поскольку вы спросили) обновлению уникальных значений индекса с другими значениями было бы обновление всех других значений в строках до значения другой строки. Это означает, что вы можете оставить уникальные значения Индекса в покое, и в конечном итоге вы получите нужные вам данные. Однако будьте осторожны, если какая-то другая таблица ссылается на эту таблицу в связи с внешним ключом, чтобы все связи в DB остались нетронутыми.

У меня та же проблема. Вот мой предлагаемый подход в PostgreSQL. В моем случае Мой уникальный индекс - это значение последовательности, определяющее явный пользовательский порядок в моих строках. Пользователь будет перемешивать строки в веб-приложении, а затем отправлять изменения.

Я планирую добавить триггер "before". В этом триггере всякий раз, когда мое уникальное значение индекса обновляется, я буду смотреть, не содержит ли уже какая-либо другая строка мое новое значение. Если это так, я отдам им свою старую ценность и фактически украду ее у них.

Я надеюсь, что PostgreSQL позволит мне сделать эту перетасовку в триггере before.

Я отправлю ответ и дам вам знать мой пробег.

Предполагая, что вы знаете PK из двух строк, которые вы хотите обновить... Это работает на сервере SQL, не может говорить о других продуктах. SQL является (предположительно) атомарным на уровне оператора:

CREATE TABLE testing
(
    cola int NOT NULL,
    colb CHAR(1) NOT NULL
);

CREATE UNIQUE INDEX UIX_testing_a ON testing(colb);

INSERT INTO testing VALUES (1, 'b');
INSERT INTO testing VALUES (2, 'a');

SELECT * FROM testing;

UPDATE testing
SET colb = CASE cola WHEN 1 THEN 'a'
                WHEN 2 THEN 'b'
                END
WHERE cola IN (1,2);

SELECT * FROM testing;

так что вы будете идти от:

cola    colb
------------
1       b
2       a

к:

cola    colb
------------
1       a
2       b

Для Oracle есть опция, DEFERRED, но вы должны добавить ее к своему ограничению.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Чтобы отложить ALL ограничений, которые могут быть отложены в течение всего сеанса, можно использовать инструкцию ALTER SESSION SET constraints=DEFERRED.

Источник

Oracle имеет отложенную проверку целостности, которая решает именно это, но она не доступна ни в SQL сервере, ни в MySQL.

В SQL Server оператор MERGE может обновить строки, которые обычно нарушают UNIQUE KEY/INDEX. (просто проверил это, потому что мне было любопытно.)

Однако вам придется использовать временную таблицу/переменную, чтобы предоставить MERGE w/ необходимые строки.

Я обычно думаю о значении, которое абсолютно не может иметь индекс в моей таблице. Обычно-для уникальных значений столбцов-это действительно легко. Например, для значений столбца 'position' (информация о порядке следования нескольких элементов) это 0.

Затем вы можете скопировать значение A в переменную, обновить его значением B, а затем установить значение B из вашей переменной. Два вопроса, но я не знаю лучшего решения.

1) переключите идентификаторы для имени

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Для входного сигнала образца, выход:

студенческий

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

"в случае n-го числа рядов как получится......"