una tablas tabla relacionar referencial primary primarias primaria por llaves llave integridad foráneas foranea externa datos creada como codigo clave agregar sql database-design primary-key

referencial - relacionar tablas en sql server por codigo



¿Las tablas de enlace necesitan un campo de clave principal sin sentido? (12)

Estoy trabajando en un par de tablas de enlaces y me puse a pensar (Danger Will Robinson, Danger) cuáles son las posibles estructuras de una tabla de enlaces y cuáles son sus pros y sus contras.

Se me ocurrieron algunas restricciones posibles para la tabla de enlaces:

Modelo tradicional de 3 columnas.

  • ID - auto numerado PRIMARIO
  • table1fk - clave externa
  • table2fk - clave externa

Es un clásico, en la mayoría de los libros, ''nuff dijo.

Modelo de 3 columnas indexadas

  • ID - auto numerado PRIMARIO
  • table1fk - INDEX (''table1fk'') clave foránea INDEX (''table1fk'')
  • table2fk - INDEX (''table2fk'') clave foránea INDEX (''table2fk'')

En mi propia experiencia, los campos en los que está consultando no están indexados en el modelo tradicional. He encontrado que indexar los campos de clave externa mejora el rendimiento como se esperaría. No es un cambio importante, pero un buen ajuste optimizador.

Clave compuesta 2 columnas ADD PRIMARY KEY (''table1fk'' , ''table2fk'')

  • table1fk - clave externa
  • table2fk - clave externa

Con esto uso una clave compuesta para que un registro de table1 solo pueda vincularse a un registro en table2 una vez. Debido a que la clave es compuesta, puedo agregar registros (1,1), (1,2), (2,2) sin ningún error de duplicación.

¿Algún problema potencial con la clave compuesta 2 columnas opción? ¿Hay un problema de indexación que esto podría causar? Un éxito de rendimiento? ¿Algo que descalifique esto como una posible opción?

Compone PK y desactiva la agrupación.

Hay algo que se llama identificación y relación no identificable. Con las relaciones de identificación, el FK es una parte del PK en la tabla de muchos a muchos. Por ejemplo, digamos que tenemos tablas Person , Company y una tabla de muchos a muchos Employment . En una relación de identificación, tanto fk PersonID como CompanyID son parte del pk, por lo que no podemos repetir la combinación de PersonID, CompanyID .

TABLE Employment(PersonID int (PK,FK), CompanyID int (PK,FK))

Ahora, supongamos que queremos capturar el historial de empleo, para que una persona pueda dejar una empresa, trabajar en otro lugar y regresar a la misma compañía más tarde. La relación no es identificable aquí, la combinación de PersonID, CompanyID ahora puede repetirse, por lo que la tabla sería algo así como:

TABLE Employment(EmploymentID int (PK), PersonID int (FK), CompanyID int (FK), FromDate datetime, ToDate datetime)

He usado ambos, el único beneficio de usar el primer modelo (con uid) es que puedes transportar el identificador como un número, mientras que en algunos casos tendrías que hacer alguna concatenación de cadenas con la clave compuesta para transportarlo. .

Estoy de acuerdo en que no indexar las claves foráneas es una mala idea, sea como sea que vayas.

He usado una clave compuesta para evitar la entrada duplicada y dejar que la base de datos maneje la excepción. Con una sola clave, confía en la aplicación de front-end para verificar si la base de datos está duplicada antes de agregar un nuevo registro.

La respuesta correcta es:

  • La clave principal es (''table1fk'' , ''table2fk'')
  • Otro índice en (''table2fk'' , ''table1fk'')

Porque:

  • No necesita un índice solo en table1fk o table2fk: el optimizador usará el PK
  • Lo más probable es que uses la tabla "de ambas maneras"
  • La adición de una clave sustituta solo es necesaria debido a los ORM de cabeza de cerebro

Para las tablas de enlace verdadero, normalmente no existen como entidades de objeto en mis modelos de objeto. Por lo tanto, la clave sustituta no se utiliza nunca. La eliminación de un elemento de una colección da como resultado la eliminación de un elemento de una relación de enlace donde se conocen ambas claves foráneas ( Person.Siblings.Remove(Sibling) o Person.RemoveSibling(Sibling) que se traduce de manera apropiada en la capa de acceso a datos como usp_Person_RemoveSibling(PersonID, SiblingID) ).

Como Mike mencionó, si se convierte en una entidad real en su modelo de objeto, entonces puede merecer una identificación. Sin embargo, incluso con la adición de factores temporales como las fechas de inicio y finalización efectivas de la relación y cosas por el estilo, no siempre está claro. Por ejemplo, la colección puede tener una fecha efectiva asociada al nivel agregado, por lo que la relación en sí misma puede no convertirse en una entidad con propiedades expuestas.

Me gustaría agregar que es posible que necesite la tabla indexada en ambos sentidos en las dos columnas de clave externa.

Si está utilizando un ORM para obtener / modificar los datos, algunos de ellos requieren una clave principal de una sola columna (Gracias, Tom H, por señalarlo) para que funcione correctamente (creo que Subsonic 2.x fue de esta manera, No estoy seguro acerca de 3.x).

En mi opinión, tener la clave principal no afecta el rendimiento en ningún grado medible, por lo que generalmente lo uso.

Si esta es una verdadera tabla de unión de muchos a muchos, entonces descarte la columna de identificación innecesaria (a menos que su ORM lo requiera. En ese caso, debe decidir si su intelecto va a superar su sentido práctico).

Pero me parece que las tablas de verdadera combinación son bastante raras. Por lo general, no pasa mucho tiempo antes de que empiece a querer poner otros datos en esa tabla. Por eso casi siempre modelo estas tablas de unión como entidades desde el principio y pegamos un ID allí.

Si necesita atravesar la tabla de unión ''en ambas direcciones'', es decir, comenzar con una tabla 1fk o una tecla table2fk solamente, puede considerar agregar un segundo índice compuesto invertido.

ADD KEY (''table2fk'', ''table1fk'')

Tener una sola columna pk puede ayudar mucho en una situación de recuperación de desastres. Entonces, aunque en teoría es correcto, solo necesitas las 2 claves externas. En la práctica, cuando la mierda golpea el ventilador es posible que desee la clave de una sola columna. Nunca he estado en una situación en la que me hubieran atornillado porque tenía un identificador de una sola columna, pero sí en la que estaba atornillado porque no lo tenía.

Yo (casi) siempre uso la clave principal adicional de una sola columna. Esto generalmente facilita la creación de interfaces de usuario, ya que cuando un usuario selecciona esa entidad de enlace en particular, puedo identificarme con un solo valor entero en lugar de tener que crear y luego analizar identificadores compuestos.

Yo usaría clave compuesta, y ninguna clave sin significado adicional.

No usaría un sistema ORM que aplique tales reglas en mi estructura de base de datos.