una - tipos de indices en sql




Intercambie valores de columna indexados únicos en la base de datos (8)

1) cambie los identificadores por nombre

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Para la entrada de muestra, la salida es:

estudiante de id

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

"en caso de que n número de filas cómo se gestionará ......"

Tengo una tabla de base de datos y uno de los campos (no la clave principal) tiene un índice único. Ahora quiero intercambiar valores bajo esta columna por dos filas. ¿Como se puede hacer esto? Dos hacks que conozco son:

  1. Elimine ambas filas y vuelva a insertarlas.
  2. Actualice las filas con algún otro valor e intercambie y luego actualice al valor real.

Pero no quiero ir por estos, ya que no parecen ser la solución adecuada al problema. ¿Podría alguien ayudarme?


Además de la respuesta de Andy Irving

esto funcionó para mí (en SQL Server 2005) en una situación similar en la que tengo una clave compuesta y necesito intercambiar un campo que es parte de la restricción única.

clave: pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2

y necesito cambiar LNUM para que el resultado sea

clave: pID, LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0

el SQL necesario:

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))

En SQL Server, la instrucción MERGE puede actualizar filas que normalmente romperían una CLAVE / ÍNDICE ÚNICO. (Solo probé esto porque tenía curiosidad).

Sin embargo, tendría que usar una tabla / variable temporal para suministrar MERGE con las filas necesarias.


Hay otro enfoque que funciona con SQL Server: use una tabla temporal para unirse a él en su instrucción UPDATE.

El problema es causado por tener dos filas con el mismo valor al mismo tiempo , pero si actualiza ambas filas a la vez (a sus nuevos valores únicos), no hay violación de restricción.

Pseudocódigo:

-- 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)

Gracias a Rich H por esta técnica. - Marca


Oracle ha diferido la comprobación de integridad que resuelve exactamente esto, pero no está disponible en SQL Server o MySQL.


Para Oracle hay una opción, DEFERRED, pero debe agregarla a su restricción.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Para diferir TODAS las restricciones que son diferibles durante toda la sesión, puede usar la instrucción ALTER SESSION SET restricciones = DEFERRED.

Source


Suponiendo que conoce el PK de las dos filas que desea actualizar ... Esto funciona en SQL Server, no se puede hablar de otros productos. SQL es (se supone que es) atómico en el nivel de instrucción:

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;

entonces irás de:

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

a:

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

También creo que el # 2 es la mejor apuesta, aunque me aseguraría de incluirlo en una transacción en caso de que algo salga mal a mitad de la actualización.

Una alternativa (ya que solicitó) para actualizar los valores del Índice Único con valores diferentes sería actualizar todos los demás valores en las filas a los de la otra fila. Hacer esto significa que puede dejar solo los valores del Índice único y, al final, terminar con los datos que desea. Sin embargo, tenga cuidado, en caso de que alguna otra tabla haga referencia a esta tabla en una relación de clave externa, que todas las relaciones en la base de datos permanecen intactas.





database