source - sql database modeler
¿Clave principal o Índice único? (15)
Además de lo que las otras respuestas han dicho, algunas bases de datos y sistemas pueden requerir que un primario esté presente. Una situación viene a la mente; cuando se utiliza la replicación empresarial con Informix, debe estar presente una PK para que una tabla participe en la replicación.
En el trabajo, tenemos una gran base de datos con índices únicos en lugar de claves principales y todo funciona bien.
Estoy diseñando una nueva base de datos para un nuevo proyecto y tengo un dilema:
En la teoría de DB, la clave primaria es un elemento fundamental, está bien, pero en proyectos REALES, ¿cuáles son las ventajas y desventajas de ambos?
¿Qué usas en proyectos?
EDITAR: ... y ¿qué pasa con las claves primarias y la replicación en el servidor MS SQL?
Casi nunca creo una tabla sin una clave primaria numérica. Si también hay una clave natural que debería ser única, también le pongo un índice único. Las uniones son más rápidas en enteros que las teclas naturales de múltiples columnas, los datos solo necesitan cambiarse en un solo lugar (las claves naturales suelen necesitar actualización, lo que es malo cuando está en clave primaria: relaciones de clave externa). Si va a necesitar replicación, use un GUID en lugar de un entero, pero en general prefiero una clave que sea legible por el usuario, especialmente si necesitan verla para distinguir entre John Smith y John Smith.
Las pocas veces que no creo una clave sustituta son cuando tengo una tabla de unión que está involucrada en una relación de muchos a muchos. En este caso, declaro ambos campos como la clave principal.
En MSSQL, las claves primarias deben aumentar monótonamente para obtener el mejor rendimiento en el índice agrupado. Por lo tanto, un entero con inserción de identidad es mejor que cualquier clave natural que podría no ser monótonamente creciente.
Hay algunas desventajas de los ÍNDICES CLUSTEROS frente a INDICES ÚNICOS.
Como ya se dijo, un ÍNDICE CLUSTER ordena físicamente los datos en la tabla.
Esto significa que cuando tiene mucho si inserta o elimina en una tabla que contiene un índice agrupado, cada vez (bueno, casi, dependiendo de su factor de relleno) cambia los datos, la tabla física debe actualizarse para mantenerse ordenada.
En tablas pequeñas relativas, esto está bien, pero cuando se llega a tablas que tienen datos valiosos de GB, y las inserciones / eliminaciones afectan la clasificación, se encontrará con problemas.
La elección de cuándo utilizar una clave primaria sustituta en lugar de una clave natural es complicada. Respuestas como, siempre o nunca, rara vez son útiles. Encuentro que depende de la situación.
Como ejemplo, tengo las siguientes tablas:
CREATE TABLE toll_booths (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
...
UNIQUE(name)
)
CREATE TABLE cars (
vin VARCHAR(17) NOT NULL PRIMARY KEY,
license_plate VARCHAR(10) NOT NULL,
...
UNIQUE(license_plate)
)
CREATE TABLE drive_through (
id INTEGER NOT NULL PRIMARY KEY,
toll_booth_id INTEGER NOT NULL REFERENCES toll_booths(id),
vin VARCHAR(17) NOT NULL REFERENCES cars(vin),
at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
amount NUMERIC(10,4) NOT NULL,
...
UNIQUE(toll_booth_id, vin)
)
Tenemos dos tablas de entidades ( toll_booths y cars ) y una tabla de transacciones ( drive_through ). La tabla toll_booth usa una clave sustituta porque no tiene ningún atributo natural que no esté garantizado para cambiar (el nombre puede cambiarse fácilmente). La tabla de cars usa una clave primaria natural porque tiene un identificador único no cambiante ( vin ). La tabla de transacciones drive_through utiliza una clave sustituta para facilitar la identificación, pero también tiene una restricción única sobre los atributos que se garantiza que serán únicos en el momento en que se inserta el registro.
http://database-programmer.blogspot.com tiene excelentes artículos sobre este tema en particular.
Las claves externas funcionan con restricciones únicas, así como claves principales. De libros en línea:
Una restricción FOREIGN KEY no tiene que estar vinculada solo a una restricción PRIMARY KEY en otra tabla; también se puede definir para hacer referencia a las columnas de una restricción ÚNICA en otra tabla
Para la replicación transaccional, necesita la clave principal. De libros en línea:
Las tablas publicadas para la replicación transaccional deben tener una clave principal. Si una tabla se encuentra en una publicación de replicación transaccional, no puede deshabilitar ningún índice asociado con columnas de clave principal. Estos índices son requeridos por replicación. Para deshabilitar un índice, primero debe soltar la tabla de la publicación.
Ambas respuestas son para SQL Server 2005.
No existe una clave primaria en la teoría de datos relacionales, por lo que su pregunta debe ser respondida en el nivel práctico.
Los índices únicos no son parte del estándar SQL. La implementación particular de un SGBD determinará cuáles son las consecuencias de declarar un índice único.
En Oracle, declarar una clave principal dará como resultado que se cree un índice único en su nombre, por lo que la pregunta es casi irrelevante. No puedo contarte sobre otros productos DBMS.
Estoy a favor de declarar una clave primaria. Esto tiene el efecto de prohibir valores NULL en la (s) columna (s) clave, así como prohibir duplicados. También estoy a favor de declarar restricciones REFERENCIAS para hacer cumplir la integridad de la entidad. En muchos casos, declarar un índice en los coulmn (s) de una clave externa acelerará las uniones. Este tipo de índice no debería ser único en general.
No hay desventajas de las claves primarias.
Para agregar solo algo de información a las respuestas de @MrWiggles y @Peter Parker, cuando la tabla no tiene clave principal, por ejemplo, no podrá editar los datos en algunas aplicaciones (terminarán diciendo algo así como no puede editar / eliminar datos sin Clave primaria). Postgresql permite que múltiples valores NULL estén en la columna UNIQUE, PRIMARY KEY no permite valores NULL. También algunos ORM que generan código pueden tener algunos problemas con tablas sin claves primarias.
ACTUALIZAR:
Hasta donde sé, no es posible replicar tablas sin claves primarias en MSSQL, al menos sin problemas ( details ).
Puedes verlo así:
Una clave principal ES única
Un valor único no tiene que ser la representación del elemento
¿Sentido?; Bueno, se usa una clave principal para identificar el elemento; si tiene una "Persona", le gustaría tener un Número de Identificación Personal (SSN o similar) que sea Principal para su Persona.
Por otro lado, la persona puede tener un correo electrónico único, pero no identifica a la persona.
Siempre tengo claves principales, incluso en tablas de relación (la mesa de la mitad de la tabla de conexión) podría tenerlos. ¿Por qué? Bueno, me gusta seguir un estándar al codificar, si la "Persona" tiene un identificador, el Auto tiene un identificador, bueno, entonces la Persona -> ¡Coche también debería tener un identificador!
Si algo es una clave principal, dependiendo de su motor de base de datos, toda la tabla se ordena por la clave principal. Esto significa que las búsquedas son mucho más rápidas en la clave principal porque no tiene que hacer ninguna desreferenciación ya que tiene que ver con cualquier otro tipo de índice. Además de eso, es solo teoría.
Siempre debe agregar una columna única id auto-indexada.
Luego, proceda a ignorar esa columna para todos los propósitos y propósitos que no sean al crear una vista actualizable o seleccionar.
Por ejemplo, nunca use la columna ID como una clave externa ni la use para buscar en una aplicación.
La clave primaria es utilizada principalmente por la base de datos para identificar de manera única cada fila en una tabla.
Es valioso para la base de datos identificar registros lógicos en lugar de registros físicos, es decir, cuál es el valor de rowid () en una selección o vista de varias tablas.
Además, considere que para crear una vista actualizable, debe incluir la clave principal de todas las tablas en la vista; es mucho más fácil incluir la única columna de identificación para cada tabla en lugar de tener que incluir todas las partes de una clave primaria compuesta para cada una. mesa.
En resumen, la base de datos y su optimizador utilizan la clave primaria; sus aplicaciones no deberían usarla para nada más que para crear vistas actualizables. Todas las búsquedas y relaciones necesarias para su aplicación deben ser respaldadas por índices en las columnas deseadas y explícitamente no deben usar la clave primaria.
En un nivel más profundo en la base de datos, típicamente rowid () puede proporcionar una clave única para cada fila, pero identifica filas físicas en una tabla, no filas lógicas: la primera puede modificarse mediante operaciones de mantenimiento, mientras que la segunda siempre identificará las filas. única fila lógica.
Es muy probable que otros índices sean en realidad asignaciones de ese valor de clave de índices a valores de clave primaria, no de rowid (). Esto permite que ocurran operaciones de clasificación física sin tener que volver a crear estos índices.
Siempre que no permita NULL para un valor, se deben manejar de la misma manera, pero el valor NULL se maneja de forma diferente en las bases de datos (AFAIK MS-SQL no permite más de un (1) valor NULL, mySQL y Oracle permiten esto , si una columna es ÚNICA) Así que debe definir esta columna NOT NULL INDICE ÚNICO
Tengo entendido que una clave principal y un índice único con una restricción no nula son las mismas (*); y supongo que uno elige uno u otro dependiendo de lo que la especificación explícitamente declara o implica (una cuestión de lo que quiere expresar y aplicar explícitamente). Si requiere exclusividad y no es nulo, conviértalo en una clave principal. Si sucede que todas las partes de un índice único no son nulas sin ningún requisito para eso, simplemente conviértalo en un índice único.
La única diferencia restante es que puede tener múltiples índices únicos no nulos, mientras que no puede tener múltiples claves primarias.
(*) Exceptuando una diferencia práctica: una clave principal puede ser la clave única predeterminada para algunas operaciones, como definir una clave externa. Ex. si se define una clave externa que hace referencia a una tabla y no proporciona el nombre de la columna, si la tabla a la que se hace referencia tiene una clave principal, entonces la clave principal será la columna a la que se hace referencia. De lo contrario, la columna a la que se hace referencia deberá tener un nombre explícito.
Otros aquí han mencionado la replicación de DB, pero no lo sé.
Unique Index puede tener un valor NULL. Crea un ÍNDICE NO CLUSTER. La clave principal no puede contener el valor NULL. Crea un ÍNDICE CLUSTERED.
¿Qué es un índice único?
Un índice único en una columna es un índice en esa columna que también impone la restricción de que no puede tener dos valores iguales en esa columna en dos filas diferentes. Ejemplo:
CREATE TABLE table1 (foo int, bar int); CREATE UNIQUE INDEX ux_table1_foo ON table1(foo); -- Create unique index on foo. INSERT INTO table1 (foo, bar) VALUES (1, 2); -- OK INSERT INTO table1 (foo, bar) VALUES (2, 2); -- OK INSERT INTO table1 (foo, bar) VALUES (3, 1); -- OK INSERT INTO table1 (foo, bar) VALUES (1, 4); -- Fails! Duplicate entry ''1'' for key ''ux_table1_foo''
La última inserción falla porque viola el índice único en la columna foo cuando intenta insertar el valor 1 en esta columna por segunda vez.
En MySQL, una restricción única permite múltiples NULLs.
Es posible crear un índice único en columnas mutiples.
Clave principal versus índice único
Cosas que son iguales
- Una clave principal implica un índice único.
Cosas que son diferentes:
- Una clave principal también implica NOT NULL, pero un índice único puede ser anulable.
- Solo puede haber una clave principal, pero puede haber múltiples índices únicos.
- Si no hay un índice agrupado definido, la clave principal será el índice agrupado.