mysql - uso - ¿Cómo crear un índice único ''a dos caras'' en dos campos?

optimizar consultas mysql

Question

¿Cómo puedo crear eficientemente un índice único en dos campos en una tabla como esta: crear tabla t (un entero, b entero);

donde cualquier combinación única de dos números diferentes no puede aparecer más de una vez en la misma fila en la tabla.

En otras palabras, si existe una fila tal que a = 1 yb = 2, no puede existir otra fila donde a = 2 yb = 1 o a = 1 yb = 2. En otras palabras, dos números no pueden aparecer juntos más de una vez en cualquier orden.

No tengo idea de cómo se llama esa restricción, de ahí el nombre ''índice único de dos lados'' en el título.

Actualización : si tengo una clave compuesta en las columnas (a, b), y existe una fila (1,2) en la base de datos, es posible insertar otra fila (2,1) sin un error. Lo que estoy buscando es una forma de evitar que el mismo par de números se use más de una vez en cualquier orden ...

Answer 1

¿Qué hay de controlar lo que entra en la mesa para que siempre almacene el número más pequeño en la primera columna y el más grande en la segunda? Siempre y cuando ''signifique'' lo mismo, por supuesto. Probablemente sea menos costoso hacerlo incluso antes de que llegue a la base de datos.

Si esto es imposible, podría guardar los campos tal como están pero hacer que se dupliquen en orden numérico en dos campos OTROS, en los que crearía la clave primaria (pseudo código-ish):

COLUMN A : 2
COLUMN B : 1

COLUMN A_PK : 1  ( if new.a < new.b then new.a else new.b )
COLUMN B_PK : 2  ( if new.b > new.a then new.b else new.a )

Esto podría hacerse fácilmente con un desencadenante (como en la respuesta de Ronald) o manejarse más arriba, en la aplicación.

Answer 2

Creo que esto solo se puede hacer usando un disparador FOR INSERT (en combinación con una restricción única en las dos columnas). No soy muy fluido en la sintaxis de MySql (mi T-SQL es mejor), así que supongo que lo siguiente contendrá algunos errores:

Editar: limpió la sintaxis para que funcione para MySQL. Además, tenga en cuenta que probablemente quiera poner esto también como un desencadenante BEFORE UPDATE (con un nombre de curso diferente).

Además, este método se basa en tener una clave primaria o única en los dos campos (es decir, este disparador solo verifica que el reverso no existe ya). No parece haber ninguna forma de arrojar un error desde un desencadenador, así que me atrevo a decir que esto es tan bueno como se pone.

CREATE TRIGGER tr_CheckDuplicates_insert
BEFORE INSERT ON t
FOR EACH ROW
BEGIN
    DECLARE rowCount INT;
    SELECT COUNT(*) INTO rowCount
                   FROM t
                   WHERE a = NEW.b AND b = NEW.a;

    IF rowCount > 0 THEN
        -- Oops, we need a temporary variable here. Oh well.
        -- Switch the values so that the key will cause the insert to fail.
        SET rowCount = NEW.a, NEW.a = NEW.b, NEW.b = rowCount;
    END IF;
END;

Answer 3

En Oracle podría usar un índice basado en funciones como este:

create unique index mytab_idx on mytab (least(a,b), greatest(a,b));

No sé mySQL, pero tal vez algo similar es posible? Por ejemplo, puede agregar 2 nuevas columnas leastab y greatestab a la tabla, con un desencadenador para mantenerlas con los valores de mínimo (a, b) y máximo (a, b), respectivamente, y luego crear un índice único en (leastab , greatestab).

Answer 4

Ver dos claves externas en lugar de primaria