27 votos

Intercambiar valores de única columna indexadas en la base de datos

Tengo una tabla de base de datos y uno de los campos (no es la clave principal) tiene un índice único en él. Ahora quiero cambiar valores en esta columna por dos filas. ¿Cómo podría hacerse esto? Dos hacks que conozco son:

  1. Borrar dos filas y vuelva a introducirlas
  2. Actualizar filas y algunos otros valor swap y luego actualizar al valor real.

Pero no quiero ir para estos ya que no parecen ser la solución adecuada al problema. ¿Alguien podría ayudarme?

11voto

wildplasser Puntos 17900

La palabra mágica es DEFERRABLE aquí:

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;

RESULTADO:

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)

3voto

Daan Puntos 3325

Creo que debería ir para la solución 2. No hay ninguna función 'swap' en cualquier variante SQL que conozco.

Si usted necesita hacer esto regularmente, sugiero la solución 1, dependiendo de cómo otras partes del software utilizan estos datos. Usted puede tener problemas de bloqueo si no eres cuidadoso.

Pero en Resumen: no hay otra solución que proporcionaste.

1voto

Yaakov Ellis Puntos 15470

Yo también creo que #2 es la mejor apuesta, aunque yo asegúrese de envolver en una transacción en caso de que algo va mal mediados de actualización.

Una alternativa (ya que se le pide) para la actualización de los valores de Índice Únicos con diferentes valores sería la actualización de todos los otros valores en las filas de la otra fila. Hacer esto significa que usted puede dejar el Único Índice de valores, y en el final, se termina con los datos que desea. Tenga cuidado sin embargo, en el caso de algunas otras referencias de la tabla esta tabla en una relación de Clave externa, que todas las relaciones en la base de datos permanecen intactos.

1voto

the.jxc Puntos 1671

Tengo el mismo problema. Aquí está mi enfoque propuesto en PostgreSQL. En mi caso, mi único índice es una secuencia de valor, la definición de un usuario explícito de orden en mis filas. El usuario shuffle filas alrededor de una web-app, a continuación, enviar los cambios.

Estoy pensando en agregar un "antes de" gatillo. En ese gatillo, cuando mi único valor del índice se actualiza, voy a buscar a ver si alguna otra fila ya tiene mi nuevo valor. Si es así, les daré mi antiguo valor, y efectivamente robar el valor fuera de ellos.

Tengo la esperanza de que PostgreSQL me va a permitir hacer esto shuffle en el antes de gatillo.

Voy a publicar de nuevo y hacerle saber mi kilometraje.

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X