unica una que primario primaria llave ejemplo datos clave caracteristicas atributo sql sql-server database relational

sql - una - ¿Cuál es la mejor práctica para las claves primarias en las tablas?



llave unica base de datos (21)

Al diseñar tablas, he desarrollado el hábito de tener una columna que es única y que hago la clave principal. Esto se logra de tres maneras dependiendo de los requisitos:

  1. Columna de identidad que se incrementa automáticamente.
  2. Identificador único (GUID)
  3. Una columna de carácter corto (x) o entero (u otro tipo numérico relativamente pequeño) que puede servir como una columna de identificador de fila

El número 3 se usaría para búsquedas bastante pequeñas; en su mayoría, las tablas leen un código de cadena de longitud estática única o un valor numérico como un año u otro número.

En su mayor parte, todas las demás tablas tendrán un entero de incremento automático o una clave primaria de identificador único.

La pregunta :-)

Recientemente comencé a trabajar con bases de datos que no tienen un identificador de fila coherente y las claves principales están agrupadas en varias columnas. Algunos ejemplos:

  • fecha / carácter
  • datetime / integer
  • datetime / varchar
  • char / nvarchar / nvarchar

¿Hay un caso válido para esto? Siempre habría definido una identidad o una columna de identificador único para estos casos.

Además hay muchas tablas sin claves primarias en absoluto. ¿Cuáles son las razones válidas, si las hay, para esto?

Estoy tratando de entender por qué las tablas se diseñaron como estaban, y me parece un gran lío, pero tal vez haya buenas razones para ello.

Una tercera pregunta para ayudarme a descifrar las respuestas: en los casos en los que se utilizan varias columnas para comprender la clave principal compuesta, ¿existe una ventaja específica de este método frente a una clave sustituta / artificial? Estoy pensando principalmente en lo que se refiere a rendimiento, mantenimiento, administración, etc.

¿Qué tiene de especial la clave principal?

¿Cuál es el propósito de una tabla en un esquema? ¿Cuál es el propósito de una clave de una tabla? ¿Qué tiene de especial la clave principal? Las discusiones en torno a las claves primarias parecen pasar por alto el hecho de que la clave primaria forma parte de una tabla y esa tabla forma parte de un esquema. Lo que es mejor para la tabla y las relaciones de la tabla debe manejar la clave que se utiliza.

Las tablas (y las relaciones de tablas) contienen datos sobre la información que desea registrar. Estos hechos deben ser autónomos, significativos, fáciles de entender y no contradictorios. Desde una perspectiva de diseño, otras tablas agregadas o eliminadas de un esquema no deberían impactar en la tabla en cuestión. Debe haber un propósito para almacenar los datos relacionados solo con la información en sí. Comprender qué se almacena en una tabla no debe requerir un proyecto de investigación científica. Ningún hecho almacenado para el mismo propósito debe almacenarse más de una vez. Las claves son una parte o la totalidad de la información que se registra, que es única, y la clave principal es la clave especialmente designada que debe ser el punto de acceso primario a la tabla (es decir, debe elegirse por la coherencia y el uso de los datos, no solo insertar) actuación).

  • ASIDE: desafortunadamente, el efecto secundario de la mayoría de las bases de datos que están siendo diseñadas y desarrolladas por los programadores de aplicaciones (que a veces lo soy) es que lo que es mejor para la aplicación o el marco de la aplicación a menudo determina la elección principal de las tablas. Esto lleva a las claves de enteros y GUID (ya que son fáciles de usar para marcos de aplicaciones) y diseños de tablas monolíticas (ya que reducen la cantidad de objetos de marcos de aplicaciones necesarios para representar los datos en la memoria). Estas decisiones de diseño de base de datos impulsadas por la aplicación conducen a problemas significativos de consistencia de los datos cuando se usan a escala. Los marcos de aplicación diseñados de esta manera, naturalmente, conducen a diseños de tablas a la vez. Los "registros parciales" se crean en tablas y los datos se completan a lo largo del tiempo. Se evita la interacción de varias mesas o cuando se usa causa datos inconsistentes cuando la aplicación funciona incorrectamente. Estos diseños conducen a datos sin sentido (o difíciles de entender), datos distribuidos en tablas (hay que mirar otras tablas para dar sentido a la tabla actual) y datos duplicados.

Se dijo que las claves primarias deben ser tan pequeñas como sea necesario. Yo diría que las llaves deben ser tan grandes como sea necesario. Debe evitarse agregar campos sin sentido al azar a una tabla. Es incluso peor hacer que una clave salga de un campo sin sentido agregado aleatoriamente, especialmente cuando destruye la dependencia de unión de otra tabla a la clave no primaria. Esto solo es razonable si no hay buenas claves candidatas en la tabla, pero esta aparición es seguramente un signo de un diseño de esquema deficiente si se usa para todas las tablas.

También se dijo que las claves primarias nunca deberían cambiar, ya que la actualización de una clave primaria siempre debería estar fuera de la cuestión. Pero actualizar es lo mismo que eliminar seguido de insertar. Con esta lógica, nunca debe eliminar un registro de una tabla con una clave y luego agregar otro registro con una segunda clave. La adición de la clave principal sustituta no elimina el hecho de que existe la otra clave en la tabla. La actualización de una clave no primaria de una tabla puede destruir el significado de los datos si otras tablas tienen una dependencia de ese significado a través de una clave sustituta (por ejemplo, una tabla de estado con una clave sustituta cuya descripción de estado ha cambiado de "Procesado" a "Cancelado"). ''Definitivamente corrompería los datos). Lo que siempre debería estar fuera de la cuestión es destruir el significado de los datos.

Habiendo dicho esto, estoy agradecido por las muchas bases de datos mal diseñadas que existen en las empresas hoy en día (behemoths de 1NF sin sentido, con código de clave sin sentido), porque eso significa que hay una cantidad infinita de trabajo para las personas que entienden el diseño adecuado de la base de datos. . Pero en el lado triste, a veces me hace sentir como Sisyphus, pero apuesto a que tenía un gran número de 401k (antes del choque). Manténgase alejado de los blogs y sitios web para preguntas importantes sobre el diseño de bases de datos. Si está diseñando bases de datos, busque CJ Date. También puede hacer referencia a Celko para SQL Server, pero solo si mantiene su nariz primero. En el lado de Oracle, referencia Tom Kyte.

Además de todas esas buenas respuestas, solo quiero compartir un buen artículo que acabo de leer, El gran debate de la clave principal .

Solo para citar algunos puntos:

El desarrollador debe aplicar algunas reglas al elegir una clave principal para cada tabla:

  • La clave principal debe identificar de forma única cada registro.
  • El valor de clave primaria de un registro no puede ser nulo.
  • El valor-clave principal debe existir cuando se crea el registro.
  • La clave principal debe permanecer estable, no puede cambiar los campos de clave principal.
  • La clave principal debe ser compacta y contener la menor cantidad de atributos posibles.
  • El valor de la clave principal no se puede cambiar.

Claves naturales (tienden a) romper las reglas. Las llaves sustitutas cumplen con las reglas. (Será mejor que leas ese artículo, ¡vale la pena tu tiempo!)

Aquí está mi propia regla de oro que me he establecido después de más de 25 años de experiencia en desarrollo.

  • Todas las tablas deben tener una clave primaria de una sola columna que se incremente automáticamente.
  • Inclúyelo en cualquier vista que se pueda actualizar.
  • La clave principal no debe tener ningún significado en el contexto de su aplicación. Esto significa que no debe ser un SKU, un número de cuenta o una identificación de empleado o cualquier otra información que sea significativa para su aplicación. Es simplemente una clave única asociada con una entidad.

La base de datos utiliza la clave principal para fines de optimización y su aplicación no debe utilizarla para nada más que para identificar una entidad en particular o para relacionarse con una entidad en particular.

Siempre con una clave principal de un solo valor hace que la ejecución de UPSERTs sea muy sencilla.

Utilice índices adicionales para admitir claves de varias columnas que tengan un significado en su aplicación.

Busco claves primarias naturales y las uso donde puedo.

Si no se pueden encontrar claves naturales, prefiero un GUID a un INT ++ porque SQL Server usa árboles, y es malo agregar siempre las claves al final en los árboles.

En tablas que son acoplamientos de muchos a muchos, uso una clave primaria compuesta de las claves externas.

Como tengo la suerte de usar SQL Server, puedo estudiar los planes de ejecución y las estadísticas con el generador de perfiles y el analizador de consultas, y descubrir cómo funcionan mis claves con mucha facilidad.

Debe usar una clave primaria "compuesta" o "compuesta" que se compone de varios campos.

Esta es una solución perfectamente aceptable, vaya here para obtener más información :)

Estaré al tanto de mi preferencia por las claves naturales; úselas siempre que sea posible, ya que le facilitarán mucho la vida de administración de la base de datos. Establecí un estándar en nuestra compañía que todas las tablas tienen las siguientes columnas:

  • ID de fila (GUID)
  • Creador (cadena; tiene un valor predeterminado del nombre del usuario actual ( SUSER_SNAME() en T-SQL))
  • Creado (DateTime)
  • Marca de tiempo

La ID de la fila tiene una clave única en cada tabla y, en cualquier caso, se genera automáticamente por fila (y los permisos impiden que alguien la edite), y está razonablemente garantizada que sea única en todas las tablas y bases de datos. Si algún sistema ORM necesita una sola clave de ID, esta es la que se debe usar.

Mientras tanto, el PK real es, si es posible, una clave natural. Mis reglas internas son algo así como:

  • Personas: use una clave sustituta, por ejemplo, INT. Si es interno, el GUID del usuario de Active Directory es una opción aceptable
  • Tablas de búsqueda (por ejemplo, códigos de estado): use un código CHAR corto; es más fácil de recordar que las INT, y en muchos casos los formularios en papel y los usuarios también lo usarán por brevedad (por ejemplo, Status = "E" para "Expired", "A" para "Approved", "NADIS" para "No Asbestos Detected En la muestra")
  • Vinculación de tablas: combinación de FK (por ejemplo EventId, AttendeeId )

Así que, idealmente, terminas con un PK natural, legible por humanos y memorable, y un GUID de una sola ID-por-tabla amigable con ORM.

Advertencia: las bases de datos que mantengo atienden a cientos de miles de registros en lugar de millones o miles de millones, por lo que si tiene experiencia en sistemas más grandes que contraindiquen mi consejo, ¡no dude en ignorarme!

Evito usar claves naturales por una simple razón: el error humano. Aunque los identificadores únicos naturales suelen estar disponibles (SSN, VIN, Número de cuenta, etc.), requieren que un humano los ingrese correctamente. Si está usando SSN como clave principal, alguien transpone un par de números durante la entrada de datos, y el error no se descubre de inmediato, entonces se enfrenta al cambio de su clave principal.

El programa de la base de datos maneja todas mis claves principales en segundo plano y el usuario nunca las conoce.

Hacemos muchas uniones y las claves primarias compuestas acaban de convertirse en una fuente de rendimiento. Un int simple o largo se ocupa de muchos problemas aunque esté introduciendo una segunda clave candidata, pero es mucho más fácil y más comprensible unirse en un campo en lugar de tres.

Las claves artificiales de los versos naturales son un tipo de debate religioso entre la comunidad de bases de datos; consulte este artículo y otros a los que se vincula. No estoy a favor de tener siempre llaves artificiales, ni de tenerlas nunca . Decidiría caso por caso, por ejemplo:

  • Estados de los EE. UU .: Iría a state_code (''TX'' para Texas, etc.), en lugar de state_id = 1 para Texas
  • Empleados: por lo general, creo un ID de empleado artificial, porque es difícil encontrar algo que funcione. El SSN o su equivalente pueden funcionar, pero podría haber problemas como un nuevo carpintero que aún no ha proporcionado su SSN.
  • Historial de salarios de los empleados: (employee_id, start_date). No crearía un employee_salary_history_id artificial. ¿Qué punto serviría (aparte de la "consistencia tonta" )

Dondequiera que se usen claves artificiales, siempre debe declarar restricciones únicas en las claves naturales. Por ejemplo, use state_id si es necesario, pero entonces es mejor que declare una restricción única en state_code, de lo contrario, seguramente terminará con:

state_id state_code state_name 137 TX Texas ... ... ... 249 TX Texas

Las tablas deben tener una clave principal todo el tiempo. Cuando no es así, debería haber sido un campo de AutoIncremento.

En algún momento, las personas omiten la clave principal porque transfieren muchos datos y puede ralentizar (dependiendo de la base de datos) el proceso. PERO, debe ser agregado después de eso.

Algún comentario sobre la tabla de enlaces , esto es correcto, es una excepción PERO los campos deben ser FK para mantener la integridad, y en algunos casos esos campos también pueden ser claves primarias si no se autoriza la duplicación de enlaces ... pero para mantenerlos en una forma simple porque la excepción es algo frecuente en la programación, la clave principal debe estar presente para mantener la integridad de sus datos.

No hay problema en hacer su clave principal desde varios campos, esa es una Clave Natural .

Puede usar una columna de Identidad (asociada con un índice único en los campos candidatos) para hacer una Clave de Sustitución .

Esa es una vieja discusión. Prefiero las llaves sustitutas en la mayoría de las situaciones.

Pero no hay excusa para la falta de una llave.

RE: EDITAR

Sí, hay mucha controversia sobre eso: D

No veo ninguna ventaja obvia en las claves naturales, además del hecho de que son la elección natural. Siempre pensará en Nombre, Número de red social - o algo así - en lugar de idPerson .

Las claves sustitutas son la respuesta a algunos de los problemas que tienen las claves naturales (por ejemplo, la propagación de cambios).

A medida que te acostumbras a los sustitutos, parece más limpio y manejable.

Pero al final, descubrirás que es solo una cuestión de gusto (o mentalidad). La gente "piensa mejor" con llaves naturales, y otras no.

Para mí, las claves naturales frente a las artificiales son una cuestión de la cantidad de lógica empresarial que desea en su base de datos. El número de Seguro Social (SSN) es un gran ejemplo.

"Cada cliente en mi base de datos tendrá, y debe, tener un SSN". Bam, hecho, conviértelo en la clave principal y termínalo. Solo recuerda cuando la regla de tu negocio cambia, estás quemado.

No me gustan las claves naturales, debido a mi experiencia con las cambiantes reglas comerciales. Pero si está seguro de que no cambiará, podría evitar algunas uniones críticas.

Si realmente desea leer todo el proceso de este debate histórico, haga una búsqueda de la "clave natural" en . Deberías volver a páginas de resultados.

Siempre uso un autonumérico o campo de identidad.

Trabajé para un cliente que usó SSN como clave principal y luego, debido a las regulaciones de HIPAA, tuve que cambiar a un "MemberID" y causó muchos problemas al actualizar las claves externas en las tablas relacionadas. Cumplir con un estándar consistente de una columna de identidad me ha ayudado a evitar un problema similar en todos mis proyectos.

Sigo algunas reglas:

  1. Las llaves primarias deben ser tan pequeñas como sea necesario. Prefiera un tipo numérico porque los tipos numéricos se almacenan en un formato mucho más compacto que los formatos de caracteres. Esto se debe a que la mayoría de las claves primarias serán claves externas en otra tabla y también se utilizarán en varios índices. Cuanto más pequeña sea su clave, más pequeño será el índice, menos páginas en el caché utilizará.
  2. Las claves primarias nunca deben cambiar. Actualizar una clave principal siempre debe estar fuera de la cuestión. Esto se debe a que es más probable que se use en múltiples índices y se use como una clave externa. La actualización de una sola clave primaria podría causar un efecto de ondulación de los cambios.
  3. NO use "la clave principal del problema" como clave principal del modelo lógico. Por ejemplo, el número de pasaporte, el número de seguridad social o el número de contrato del empleado, ya que estas "claves principales" pueden cambiar para situaciones reales.

En clave sustituta vs natural, me refiero a las reglas anteriores. Si la clave natural es pequeña y nunca cambiará, puede usarse como clave principal. Si la clave natural es grande o es probable que cambie, utilizo claves sustitutas. Si no hay una clave principal, sigo creando una clave sustituta porque la experiencia muestra que siempre agregará tablas a su esquema y desearía que pusiera una clave principal en su lugar.

Solo un comentario extra sobre algo que a menudo se pasa por alto. A veces, no usar una clave sustituta tiene beneficios en las tablas secundarias. Digamos que tenemos un diseño que le permite ejecutar varias compañías dentro de una base de datos (tal vez sea una solución alojada, o lo que sea).

Digamos que tenemos estas tablas y columnas:

Company: CompanyId (primary key) CostCenter: CompanyId (primary key, foreign key to Company) CostCentre (primary key) CostElement CompanyId (primary key, foreign key to Company) CostElement (primary key) Invoice: InvoiceId (primary key) CompanyId (primary key, in foreign key to CostCentre, in foreign key to CostElement) CostCentre (in foreign key to CostCentre) CostElement (in foreign key to CostElement)

En caso de que el último bit no tenga sentido, Invoice.CompanyId es parte de dos claves externas, una para la tabla de CostCentre y otra para la tabla de CostElement . La clave principal es ( InvoiceId , CompanyId ).

En este modelo, no es posible arruinar y hacer referencia a CostElement de una compañía y a CostCentre de otra compañía. Si se utilizara una clave sustituta en las tablas CostElement y CostCentre , sería.

Cuantas menos posibilidades de arruinar, mejor.

Sospecho que la terapia de periódico enrollada de Steven A. Lowe''s es necesaria para el diseñador de la estructura de datos original.

Como un aparte, los GUIDs como clave principal pueden ser un cerdo de rendimiento. Yo no lo recomendaría.

Todas las tablas deben tener una clave primaria. De lo contrario, lo que tiene es un HEAP; esto, en algunas situaciones, puede ser lo que desea (carga pesada de inserción cuando los datos se replican a través de un agente de servicios en otra base de datos o tabla, por ejemplo).

Para las tablas de búsqueda con un volumen bajo de filas, puede usar un código 3 CHAR como clave principal, ya que esto requiere menos espacio que un INT, pero la diferencia de rendimiento es insignificante. Aparte de eso, siempre usaría un INT a menos que tenga una tabla de referencia que quizás tenga una clave primaria compuesta formada por claves externas de tablas asociadas.

Una clave natural, si está disponible, suele ser la mejor. Por lo tanto, si datetime / char identifica de forma única la fila y ambas partes son significativas para la fila, eso es genial.

Si solo la fecha y hora es significativa, y el carácter se añade para que sea único, también puede ir con un campo de identificación.

Yo también siempre uso una columna de identificación numérica. En Oracle utilizo el número (18,0) sin ninguna razón real por encima del número (12,0) (o lo que sea un int en lugar de un largo), tal vez no quiero preocuparme por obtener unos cuantos miles de millones de filas en la db!

También incluyo una columna creada y modificada (tipo timestamp) para el seguimiento básico, donde me parece útil.

No me importa configurar restricciones únicas en otras combinaciones de columnas, pero me gusta mucho mi ID, los requisitos de línea de base creados y modificados.