database - rtconner - Se permite NULL en la clave principal: ¿por qué y en qué DBMS?

cviebrock eloquent taggable (6)

Bueno, podría permitirle implementar el patrón de objeto nulo de forma nativa dentro de la base de datos. Por lo tanto, si estaba utilizando algo similar en el código, que interactuaba muy estrechamente con la base de datos, podría simplemente buscar el objeto correspondiente a la clave sin tener que realizar una comprobación de nulos en un caso especial.

Ahora, si esta funcionalidad vale la pena, no estoy seguro, pero en realidad es una cuestión de si los pros de rechazar las teclas nulas en absolutamente todos los casos superan los inconvenientes de obstruir a alguien que (para bien o para mal) realmente quiere usar las teclas nulas. Esto solo valdría la pena si pudiera demostrar alguna mejora no trivial (como una búsqueda de claves más rápida) de poder garantizar que las claves no sean nulas. Algunos motores de bases de datos mostrarían esto, otros podrían no hacerlo. Y si no hay ninguna ventaja real de forzar esto, ¿por qué restringir artificialmente a sus clientes?

Más allá de mi pregunta "¿Por qué usar ''no clave nula primaria'' en TSQL?" ...

Como entendí de otras discusiones, algunos RDBMS (por ejemplo, SQLite, ~~MySQL~~ ) permite "único" NULL en la clave principal.

¿Por qué está permitido y cómo podría ser útil?

Antecedentes: creo que es beneficioso para la comunicación con colegas y profesionales de bases de datos conocer las diferencias en los conceptos fundamentales, los enfoques y sus implementaciones en diferentes DBMS.

Notas

MySQL es rehabilitado y regresó a la lista "NOT NULL PK".
SQLite se ha agregado (gracias a Paul Hadfield) a la lista "NULL PK":

A los efectos de determinar la singularidad de los valores de clave principal, los valores NULL se consideran distintos de todos los demás valores, incluidos otros NULL.
Si una declaración INSERT o UPDATE intenta modificar el contenido de la tabla para que dos o más filas presenten valores de clave principal idénticos, es una violación de restricción. De acuerdo con el estándar de SQL, la CLAVE PRINCIPAL siempre debe implicar NOT NULL. Desafortunadamente, debido a una supervisión de codificación de larga data, este no es el caso en SQLite.
A menos que la columna sea una CLAVE PRIMARIA INTEGER, SQLite permite valores NULL en una columna CLAVE PRIMARIA . Podríamos cambiar SQLite para cumplir con el estándar (y podríamos hacerlo en el futuro), pero en el momento en que se descubrió el descuido, SQLite estaba en un uso tan amplio que temíamos romper el código heredado si solucionábamos el problema.
Así que por ahora hemos optado por continuar permitiendo NULL en las columnas de CLAVE PRIMARIA. Sin embargo, los desarrolladores deben ser conscientes de que podemos cambiar SQLite para cumplir con el estándar SQL en el futuro y deberíamos diseñar nuevos programas en consecuencia.
- SQL como lo entiende SQLite: CREAR TABLA

Como se discutió en otras respuestas, NULL pretendía significar que "la información que debe ir en esta columna es desconocida". Sin embargo, también se utiliza con frecuencia para indicar un significado alternativo de "este atributo no existe". Esta es una interpretación particularmente útil cuando se observan campos de marca de tiempo que se interpretan como la hora en que ocurrió un evento en particular, en cuyo caso a menudo se usa NULL para indicar que el evento aún no ha ocurrido.

Es un problema que SQL no admita esta interpretación muy bien; para que esto funcione correctamente, realmente necesita tener un valor separado (algo como "nunca") que no se comporta como lo hace el nulo ("nunca" debería ser igual a "nunca" y debe compararse como más alto que todos los demás valores). Pero como SQL carece de esta noción, y no hay una forma conveniente de agregarla, usar null para este propósito es a menudo la mejor opción.

Esto deja el problema de que cuando una marca de tiempo de un evento que puede no haber ocurrido debería ser parte de la clave principal de una tabla (un requisito común tal vez sea el uso de una clave natural junto con una marca de tiempo de eliminación cuando se utiliza la eliminación suave con un requisito) para la capacidad de recrear el elemento después de la eliminación) realmente desea que la clave principal tenga una columna que pueda contener nulos. Lamentablemente, esto no está permitido en la mayoría de las bases de datos, y en su lugar, debe recurrir a una clave primaria artificial (por ejemplo, un número de secuencia de fila) y una restricción ÚNICA para lo que de otro modo debería haber sido su clave principal real.

Un escenario de ejemplo, para aclarar esto: tengo una tabla de users . Como requiero que cada usuario tenga un nombre de usuario distinto, decido usar el username como la clave principal. Quiero admitir la eliminación de usuarios, pero como necesito hacer un seguimiento de la existencia de usuarios históricamente para fines de auditoría, utilizo la eliminación suave (en la primera versión del esquema, agrego una marca ''eliminada'' al usuario y me aseguro de que se elimine La marca está marcada en todas las consultas donde solo se esperan usuarios activos).

Sin embargo, un requisito adicional es que si se elimina un nombre de usuario, debería estar disponible para que los nuevos usuarios se registren. Una forma atractiva de lograr esto sería hacer que la marca eliminada cambie a una marca de tiempo anulable (donde los valores nulos indican que el usuario no se ha eliminado) y poner esto en la clave principal. Si las claves primarias permitieran columnas anulables, esto tendría el siguiente efecto:

La creación de un nuevo usuario con un nombre de usuario existente cuando la columna deleted ese usuario sea nula se denegaría como una entrada de clave duplicada
La eliminación de un usuario cambia su clave (lo que requiere cambios en cascada para las claves externas que hacen referencia al usuario, que es subóptimo, pero si las eliminaciones son poco frecuentes, es aceptable), de modo que la columna deleted es una marca de tiempo para el momento en que se produjo la eliminación
Ahora se puede crear con éxito un nuevo usuario (que tendría una marca de tiempo deleted nula).

Sin embargo, esto no se puede lograr realmente con el SQL estándar, por lo que, en su lugar, se debe usar una clave principal diferente (probablemente en este caso, una identificación numérica de usuario) y usar una restricción ÚNICA para imponer la unicidad de ( username de username , deleted ).

En lo que respecta a la teoría de bases de datos relacionales:

La clave principal de una tabla se utiliza para identificar de forma única todas y cada una de las filas de la tabla
Un valor NULO en una columna indica que no sabe cuál es el valor
Por lo tanto, nunca debe usar el valor de "No sé" para identificar de forma única una fila en una tabla.

Dependiendo de los datos que esté modelando, se puede usar un valor "inventado" en lugar de NULL. He usado 0, "N / A", ''1 de enero de 1980'' y valores similares para representar datos ficticios "que faltan".

La mayoría, si no todos, los motores de base de datos permiten una restricción o un índice ÚNICOS, lo que sí permite valores de columna NULOS, aunque (idealmente) solo se puede asignar un valor nulo a una fila (de lo contrario no sería un valor único). Esto se puede usar para soportar situaciones irritantemente pragmáticas (pero ocasionalmente necesarias) que no encajan perfectamente en la teoría relacional.

Supongamos que tiene una clave principal que contiene una columna anulable Kn.

Si desea que se rechace una segunda fila debido a que en esa segunda fila, Kn es nulo y la tabla ya contiene una fila con Kn null, entonces en realidad requiere que el sistema trate la comparación "row1.Kn = row2 .Kn "como dar VERDADERO (porque de alguna manera quiere que el sistema detecte que los valores clave en esas filas son iguales). Sin embargo, esta comparación se reduce a la comparación "null = null", y el estándar ya especifica explícitamente que null no se compara con nada, incluso a sí mismo.

Permitir lo que usted desea, equivaldría a desviarse de SQL de sus propios principios con respecto al tratamiento de nulo. Existen innumerables inconsistencias en SQL, pero esta en particular nunca pasó del comité.

Tener una clave primaria nula puede ser beneficioso en algunos escenarios. En uno de mis proyectos utilicé esta función durante la sincronización de bases de datos: una en el servidor y muchas en dispositivos de diferentes usuarios. Teniendo en cuenta el hecho de que no todos los usuarios tienen acceso a Internet todo el tiempo, decidí que solo la base de datos principal podría proporcionar identificaciones a mis entidades. SQLite tiene su propio mecanismo para numerar filas. Si hubiera usado un campo de identificación adicional, usaría más ancho de banda. Tener nulo como identificación no solo me notifica que se ha creado una entidad en el dispositivo de los clientes cuando no tenía acceso a Internet, sino que también reduce la complejidad del código. El único inconveniente es que en el dispositivo de los clientes no puedo obtener una entidad por su ID a menos que se haya sincronizado previamente con la base de datos principal. Sin embargo, eso no es un problema ya que mi usuario se preocupa por las entidades por sus parámetros, no por su ID única.

No sé si las versiones anteriores de MySQL difieren en esto, pero a partir de las versiones modernas, una clave principal debe estar en columnas que no sean nulas. Consulte dev.mysql.com/doc/refman/5.1/en/create-table.html : "UNA PRIMARY KEY es un índice único donde todas las columnas clave deben definirse como NOT NULL . Si no se declaran explícitamente como NOT NULL , MySQL las declara tan implícitamente (y en silencio)".