¿Una clave externa crea automáticamente un índice?

MS SQL Server 2000

Me siento tonto por hacer esta pregunta, pero me han dicho que si selecciono dos tablas, ese SQL Server creará algo parecido a un índice en la tabla secundaria. Me cuesta creer que esto sea cierto, pero no puedo encontrar mucho relacionado específicamente con esto.

Mi verdadera razón para preguntar esto es porque estamos experimentando un tiempo de respuesta muy lento en una statement de eliminación en comparación con una tabla que probablemente tiene 15 tablas relacionadas. Le pregunté a nuestro tipo de base de datos y él dice que si hay una clave externa en los campos, entonces actúa como un índice. ¿Cuál es tu experiencia con esto? ¿Debo agregar índices en todos los campos de clave externa o solo son gastos indirectos innecesarios?

Una clave foránea es una restricción, una relación entre dos tablas, que no tiene nada que ver con un índice per se.

Pero es un hecho conocido que tiene mucho sentido indexar todas las columnas que forman parte de cualquier relación de clave externa, porque a través de una relación FK, a menudo necesitará buscar una tabla relacionada y extraer ciertas filas basadas en un solo valor o un rango de valores.

Entonces, tiene sentido indexar cualquier columna involucrada en un FK, pero un FK per se no es un índice.

Vea el excelente artículo de Kimberly Tripp “¿Cuándo dejó SQL Server de poner índices en las columnas de clave externa?” .

Wow, las respuestas están por todo el mapa. Entonces la Documentación dice:

Una restricción FOREIGN KEY es un candidato para un índice porque:

  • Los cambios en las restricciones PRIMARY KEY se verifican con restricciones FOREIGN KEY en tablas relacionadas.

  • Las columnas de clave externa a menudo se utilizan en criterios de combinación cuando los datos de tablas relacionadas se combinan en consultas al hacer coincidir las columnas en la restricción FOREIGN KEY de una tabla con la columna clave principal o única en la otra tabla. Un índice permite que Microsoft® SQL Server ™ 2000 encuentre rápidamente datos relacionados en la tabla de claves foráneas. Sin embargo, crear este índice no es un requisito. Los datos de dos tablas relacionadas se pueden combinar incluso si no se definen restricciones PRIMARY KEY o KEY FOREIGN entre las tablas, pero una relación de clave externa entre dos tablas indica que las dos tablas se han optimizado para combinarse en una consulta que utiliza las claves como sus criterios

Entonces parece bastante claro (aunque la documentación es un poco confusa) que de hecho no crea un índice.

No, no existe un índice implícito en los campos de clave externa; de lo contrario, Microsoft dirá “Crear un índice en una clave externa suele ser útil” . Su colega puede estar confundiendo el campo de la clave externa en la tabla de referencia con la clave primaria en la tabla referida, las claves primarias crean un índice implícito.

Supongamos que tiene una gran mesa llamada pedidos y una pequeña mesa llamada clientes. Hay una clave externa de un pedido a un cliente. Ahora, si elimina un cliente, el servidor Sql debe verificar que no haya pedidos huérfanos; si hay, genera un error.

Para verificar si hay algún pedido, Sql Server debe buscar en la tabla de grandes pedidos. Ahora, si hay un índice, la búsqueda será rápida; si no lo hay, la búsqueda será lenta.

Entonces, en este caso, la eliminación lenta podría explicarse por la ausencia de un índice. Especialmente si Sql Server tendría que buscar 15 tablas grandes sin un índice.

PD Si la clave externa tiene ON DELETE CASCADE, el servidor Sql aún debe buscar en la tabla de órdenes, pero luego debe eliminar todas las órdenes que hacen referencia al cliente eliminado.

SQL Server autocrea índices para claves primarias, pero no para claves externas. Crea el índice para las claves externas. Probablemente vale la pena la sobrecarga.

Las claves foráneas no crean índices. Solo las restricciones de clave alternativa (UNIQUE) y las restricciones de clave primaria crean índices. Esto es cierto en Oracle y SQL Server.

No que yo sepa. Una clave externa solo agrega una restricción que el valor en la clave secundaria también se representa en algún lugar de la columna padre. No le está diciendo a la base de datos que la clave secundaria también necesita ser indexada, solo restringida.

Estrictamente hablando, las claves externas no tienen absolutamente nada que ver con los índices, sí. Pero, como señalaron los oradores arriba mencionados, tiene sentido crear uno para acelerar las búsquedas FK. De hecho, en MySQL, si no especifica un índice en su statement de FK, el motor (InnoDB) lo crea automáticamente.

En PostgeSql puede verificar los índices usted mismo si pulsa \ d tablename

Verá que los índices btree se han creado automáticamente en columnas con clave primaria y restricciones únicas, pero no en columnas con claves externas.

Creo que eso responde tu pregunta al menos para postgres.

Observé que Entity Framework 6.1 señalaba que MSSQL agrega automáticamente índices en claves externas.