mysql - first - Clave única con NULLs

not null mysql create table

Question

Esta pregunta requiere algunos antecedentes hipotéticos. Consideremos una tabla de employee que tiene el name columnas, date_of_birth , date_of_birth , title , salary , utilizando MySQL como RDBMS. Dado que si una persona dada tiene el mismo nombre y fecha de nacimiento que otra persona, son, por definición, la misma persona (a menos que haya coincidencias asombrosas donde tenemos dos personas llamadas Abraham Lincoln nacidas el 12 de febrero de 1809), pondremos una clave única en name y fecha de date_of_birth que significa "no almacene la misma persona dos veces". Ahora considera estos datos:

id name        date_of_birth title          salary
 1 John Smith  1960-10-02    President      500,000
 2 Jane Doe    1982-05-05    Accountant      80,000
 3 Jim Johnson NULL          Office Manager  40,000
 4 Tim Smith   1899-04-11    Janitor         95,000

Si ahora trato de ejecutar la siguiente declaración, debería y fallará:

INSERT INTO employee (name, date_of_birth, title, salary)
VALUES (''Tim Smith'', ''1899-04-11'', ''Janitor'', ''95,000'')

Si intento este, tendrá éxito:

INSERT INTO employee (name, title, salary)
VALUES (''Jim Johnson'', ''Office Manager'', ''40,000'')

Y ahora mis datos se verán así:

id name        date_of_birth title          salary
 1 John Smith  1960-10-02    President      500,000
 2 Jane Doe    1982-05-05    Accountant      80,000
 3 Jim Johnson NULL          Office Manager  40,000
 4 Tim Smith   1899-04-11    Janitor         95,000
 5 Jim Johnson NULL          Office Manager  40,000

Esto no es lo que quiero, pero no puedo decir que esté totalmente en desacuerdo con lo que sucedió. Si hablamos en términos de conjuntos matemáticos,

{''Tim Smith'', ''1899-04-11''} = {''Tim Smith'', ''1899-04-11''} <-- TRUE
{''Tim Smith'', ''1899-04-11''} = {''Jane Doe'', ''1982-05-05''} <-- FALSE
{''Tim Smith'', ''1899-04-11''} = {''Jim Johnson'', NULL} <-- UNKNOWN
{''Jim Johnson'', NULL} = {''Jim Johnson'', NULL} <-- UNKNOWN

Supongo que MySQL dice: "Como no sé si Jim Johnson con fecha de nacimiento NULL no está ya en esta tabla, lo agregaré".

Mi pregunta es: ¿Cómo puedo evitar los duplicados aunque no siempre se conozca date_of_birth ? Lo mejor que he encontrado hasta ahora es mover date_of_birth a una tabla diferente. El problema con eso, sin embargo, es que podría terminar con, digamos, dos cajeros con el mismo nombre, título y salario, diferentes fechas de nacimiento y ninguna manera de almacenarlos sin tener duplicados.

Answer 1

Creo que MySQL lo hace aquí. Algunas otras bases de datos (por ejemplo, Microsoft SQL Server) tratan a NULL como un valor que solo se puede insertar una vez en una columna ÚNICA, pero personalmente encuentro que este es un comportamiento extraño e inesperado.

Sin embargo, dado que esto es lo que desea, puede usar algún valor "mágico" en lugar de NULL, como una fecha que ha pasado mucho tiempo en el pasado.

Answer 2

En palabras simples, el rol de la restricción Única es hacer que el campo o la columna. El nulo destruye esta propiedad ya que la base de datos trata el nulo como desconocido

Inorder para evitar duplicados y permitir null:

Hacer clave única como clave principal

Answer 3

Hay otra forma de hacerlo. Adición de una columna (no anulable) para representar el valor de String de date_of_birth column. El nuevo valor de la columna sería "" (cadena vacía) si date_of_birth es nulo.

Nombramos la columna como date_of_birth_str y creamos un empleado exclusivo de restricción (nombre, date_of_birth_str). Por lo tanto, cuando dos grabados vienen con el mismo nombre y un valor de fecha_de_birth nulo, la restricción única aún funciona.

Pero los esfuerzos de mantenimiento para las dos columnas del mismo significado y el daño de rendimiento de la nueva columna deben considerarse cuidadosamente.

Answer 4

La solución perfecta sería el soporte para el Reino Unido basado en funciones, pero eso se vuelve más complejo ya que mySQL también tendría que admitir índices basados ​​en funciones. Esto evitaría la necesidad de usar valores "falsos" en lugar de NULL, al mismo tiempo que permitiría a los desarrolladores la capacidad de decidir cómo tratar los valores NULL en el Reino Unido. Desafortunadamente, mySQL no admite actualmente la funcionalidad que conozco, por lo que nos quedamos con soluciones.

CREATE TABLE employee( 
 name CHAR(50) NOT NULL, 
 date_of_birth DATE, 
 title CHAR(50), 
 UNIQUE KEY idx_name_dob (name, IFNULL(date_of_birth,''0000-00-00 00:00:00''))
);

(Tenga en cuenta el uso de la función IFNULL () en la definición de clave única)

Answer 5

Recomiendo crear una checksum columna de tabla adicional que contenga el hash md5 de name y date_of_birth . Soltar clave única (name, date_of_birth) porque no resuelve el problema. Crear una clave única en la suma de comprobación.

ALTER TABLE employee 
    ADD COLUMN checksum CHAR(32) NOT NULL;

UPDATE employee 
SET checksum = MD5(CONCAT(name, IFNULL(date_of_birth, '''')));

ALTER TABLE employee 
    ADD UNIQUE (checksum);

Esta solución crea una pequeña sobrecarga técnica, ya que cada par insertado necesita generar hash (lo mismo para cada consulta de búsqueda). Para otras mejoras, puede agregar un activador que generará hash para usted en cada inserción:

CREATE TRIGGER before_insert_employee 
BEFORE INSERT ON employee
FOR EACH ROW
    IF new.checksum IS NULL THEN
      SET new.checksum = MD5(CONCAT(new.name, IFNULL(new.date_of_birth, '''')));
    END IF;

Answer 6

Su problema de no tener duplicados basados ​​en el nombre no se puede resolver porque no tiene una clave natural. Poner una fecha falsa en personas cuya fecha de nacimiento es desconocida no resolverá su problema. John Smith, nacido en 1900/01/01, seguirá siendo una persona diferente a John Smithh, nacido en 1960/03/09.

Trabajo con datos de nombres de organizaciones grandes y pequeñas todos los días y puedo asegurarles que tienen dos personas diferentes con el mismo nombre todo el tiempo. A veces con el mismo título de trabajo. La fecha de nacimiento tampoco es garantía de singularidad, muchos de los herreros de John Smith nacieron en la misma fecha. Al trabajar con los datos de la oficina de los médicos, a menudo tenemos dos médicos con el mismo nombre, dirección y número de teléfono (combinación de padre e hijo)

Su mejor apuesta es tener una identificación de empleado si está insertando datos de empleados para identificar a cada empleado de manera única. Luego, verifique el nombre de uniquen en la interfaz de usuario y si hay una o más coincidencias, pregúntele al usuario si se refirió a ellas y si dice que no, inserte el registro. Luego construya un proceso de reducción de problemas para solucionar problemas si a alguien se le asignan dos ID por accidente.

Answer 7

Tuve un problema similar a esto, pero con un giro. En su caso, todos los empleados tienen un cumpleaños, aunque puede ser desconocido. En ese caso, tiene sentido lógico que el sistema asigne dos valores para los empleados con cumpleaños desconocidos pero de otra manera información idéntica. La respuesta aceptada de NealB es muy precisa.

Sin embargo, el problema que encontré era uno en el que el campo de datos no tenía necesariamente un valor. Por ejemplo, si agregó un campo ''name_of_spouse'' a su tabla, no necesariamente habrá un valor para cada fila de la tabla. En ese caso, el primer punto de bala de NealB (la "manera incorrecta") realmente tiene sentido. En este caso, se debe insertar una cadena ''Ninguna'' en la columna name_of_spouse para cada fila en la que no haya cónyuge conocido.

La situación en la que me encontré con este problema fue al escribir un programa con base de datos para clasificar el tráfico de IP. El objetivo era crear un gráfico de tráfico IP en una red privada. Cada paquete se colocó en una tabla de base de datos con un índice de conexión único basado en su origen y destino de ip, origen y destino de puerto, protocolo de transporte y protocolo de aplicación. Sin embargo, muchos paquetes simplemente no tienen un protocolo de aplicación. Por ejemplo, todos los paquetes TCP sin un protocolo de aplicación deben clasificarse juntos y deben ocupar una entrada única en el índice de conexiones. Esto se debe a que quiero que esos paquetes formen un solo borde de mi gráfico. En esta situación, tomé mi propio consejo desde arriba y almacené una cadena ''Ninguna'' en el campo del protocolo de la aplicación para asegurar que estos paquetes formaran un grupo único.

Answer 8

Una propiedad fundamental de una clave única es que debe ser única. Haciendo parte de esa clave, Nullable destruye esta propiedad.

Hay dos soluciones posibles para su problema:

• Una forma, la manera equivocada, sería usar alguna fecha mágica para representar lo desconocido. Esto solo lo lleva más allá del "problema" de DBMS pero no resuelve el problema en un sentido lógico. Espere problemas con dos entradas "John Smith" que tengan fechas de nacimiento desconocidas. ¿Son estos chicos uno y el mismo o son individuos únicos? Si sabe que son diferentes, entonces vuelve al mismo problema anterior: su clave única no es única. Ni siquiera pienses en asignar un rango completo de citas mágicas para representar "desconocido", este es verdaderamente el camino al infierno.

• Una mejor manera es crear un atributo EmployeeId como una clave sustituta. Esto es solo un identificador arbitrario que asigna a personas que sabe que son únicas. Este identificador es a menudo sólo un valor entero. Luego cree una tabla de Empleado para relacionar el ID de empleado (clave única, que no acepta nulos) con lo que cree que son los atribuibles dependientes, en este caso, Nombre y Fecha de nacimiento (cualquiera de los cuales puede contener nulos). Use la clave sustituta de EmployeeId en todos los lugares en los que haya usado anteriormente el Nombre / Fecha de nacimiento. Esto agrega una nueva tabla a su sistema pero resuelve el problema de valores desconocidos de una manera robusta.