Pregunta de MySQL-Clave única ¿No funciona correctamente, o estoy malentendido?

nullable composite-key

Question

Intento crear una relación donde se pueda incluir cualquiera de las cuatro partes diferentes, pero cualquier colección de las mismas partes debe tratarse como única.

Ejemplo: una tarea debe tener una empresa asignada, opcionalmente puede tener una ubicación asignada, grupo de trabajo y programa. Una tarea puede no tener un grupo de trabajo sin una ubicación.

Supongamos que tenemos empresas A, B, C; ubicaciones X, Y, Z; grupos de trabajo I, J, K y programas 1, 2, 3.

Entonces, las relaciones válidas podrían incluir A - X - I - 1 A - Z - 2 B - Y C C - 3 B - Z - K

Pero las relaciones inválidas incluirían A - K (Grupo de trabajo sin ubicación) Y - K - 1 (Sin compañía)

Entonces, para crear mi mesa, he creado

companyID INT NOT NULL,
FOREIGN KEY companyKEY (companyID) REFERENCES company (companyID),
locationID INT,
FOREIGN KEY locationKEY (locationID) REFERENCES location (locationID),
workgroupID INT,
FOREIGN KEY workgroupKEY (workgroupID) REFERENCES workgroup (workgroupID),
programID INT,
FOREIGN KEY programKEY (programID) REFERENCES program (programID),
UNIQUE KEY companyLocationWorkgroupProgramKEY (companyID, locationID, workgroupID, programID)

Me imagino que esto manejaría todas mis relaciones además de la necesidad de una asignación de tener una ubicación si hay un grupo de trabajo (lo cual puedo hacer felizmente programáticamente o con activadores, creo)

Sin embargo, cuando pruebo este esquema, me permite ingresar lo siguiente ...

INSERT INTO test VALUES (1, null, null, null), (1, null, null, null);

... sin quejas. Supongo que (1, nulo, nulo, nulo) no se iguala porque se incluyen valores nulos. Si este es el caso, ¿hay alguna forma de que pueda manejar esta relación?

¡Cualquier ayuda sería apreciada!

Answer 1

En MySQL NULL! = NULL, o cualquier cosa. Entonces eso es lo que el UNIQUE no funciona. Debe usar otro valor predeterminado para los espacios en blanco, como cero

Answer 2

Esta es una característica (aunque no es lo que esperaba, tampoco).

Este hilo sugiere hacer que su clave sea la principal para obtener el comportamiento esperado:

Esta es una característica: un valor NULL es un valor indefinido, por lo tanto, dos valores NULL no son lo mismo. Puede ser un poco confuso pero tiene sentido cuando lo piensas.

Un índice ÚNICO asegura que los valores no NULL sean únicos; podría especificar que su columna no acepte valores NULOS.

Answer 3

Creo que es importante tener en cuenta que existe una forma adecuada para interpretar y manejar valores NULL, y el comportamiento exhibido por el OP es exactamente lo que se pretende. Puede ignorar ese comportamiento, y puede manejar su consulta de la forma que desee sin objeción por mi parte, pero podría ser conveniente "Aceptar" una respuesta que describa alguna forma de Mejores Prácticas, en lugar de una preferencia personal no estándar.

O si no está de acuerdo con las Mejores prácticas de consenso, simplemente no puede Aceptar ninguna respuesta.

No es una carrera para obtener una respuesta lo más rápido posible. La deliberación y la colaboración también pretenden ser parte del proceso, creo.

Answer 4

La única forma en que puedo pensar en manejar esto sin desencadenadores / programación adicionales sería tener un único valor "Ninguno de los anteriores" en cada una de las tablas a las que se hace referencia, para que su prueba se vea como

INSERT INTO test VALUES (1, NO_LOCATION, NO_WORKGROUP, NO_PROGRAM),
(1, NO_LOCATION, NO_WORKGROUP, NO_PROGRAM)

Donde los identificadores NO_* son el tipo / longitud correcto para sus columnas de ID. Esto luego fallaría, como es de esperar.