Claves foráneas SQL Server: índices y validación confiable

Una carga termina después de deshabilitar temporalmente las claves foráneas. La aplicación continúa y los nuevos registros parecen comprobarse. Meses después, eliminar un cliente tarda demasiado y los informes muestran pedidos sin cliente. Se han mezclado dos problemas: validar datos existentes y localizar eficientemente las filas relacionadas.

Una clave foránea no crea automáticamente un índice en las columnas de la tabla hija. La clave primaria o única del padre proporciona una referencia válida, pero SQL Server puede necesitar buscar hijos al actualizar o eliminar el padre. Una modificación pequeña termina así recorriendo una tabla enorme.

Distinguir habilitación y confianza

Ejecuta esta consulta en la base afectada. La visibilidad de metadatos depende de los permisos. Una lista vacía bajo una identidad restringida no demuestra que todas las relaciones estén correctamente definidas.

SELECT
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ChildSchema,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    fk.name,
    fk.is_disabled,
    fk.is_not_trusted,
    fk.delete_referential_action_desc
FROM sys.foreign_keys AS fk
WHERE fk.is_disabled = 1 OR fk.is_not_trusted = 1;

Una restricción habilitada puede seguir sin confianza. Habilitarla sin comprobar las filas antiguas puede proteger cambios futuros y conservar violaciones históricas. El optimizador no puede utilizar las mismas suposiciones que permitiría una relación validada. Un INSERT nuevo correcto no demuestra que los datos anteriores cumplan la regla.

Una restricción deshabilitada es otro estado: las escrituras futuras tampoco reciben esa comprobación. Documenta por qué se deshabilitó y qué datos cambiaron desde entonces. Si la aplicación siguió funcionando, revisar únicamente el lote de importación original deja parte del problema sin examinar.

Corregir datos antes de validar

Las instrucciones siguientes son un patrón para adaptar a tablas de práctica existentes dbo.Orders y dbo.Customers. Confirma los nombres y la relación real. El anti-join detecta identificadores de cliente no nulos sin padre; no decide si debes borrar pedidos, corregir su clave o recuperar clientes ausentes.

SELECT o.CustomerId, COUNT_BIG(*) AS OrphanRows
FROM dbo.Orders AS o
LEFT JOIN dbo.Customers AS c ON c.CustomerId=o.CustomerId
WHERE o.CustomerId IS NOT NULL AND c.CustomerId IS NULL
GROUP BY o.CustomerId;

CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);

ALTER TABLE dbo.Orders
WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;

Los dos CHECK tienen funciones distintas. WITH CHECK valida los registros existentes; CHECK CONSTRAINT habilita la restricción. Esa validación puede leer muchos datos y adquirir bloqueos. Estima el trabajo y revisa después is_disabled e is_not_trusted. Los valores del catálogo ofrecen mejor evidencia que recordar haber habilitado la restricción.

Una columna hija nullable puede contener NULL para representar ausencia de relación. No declares huérfana cada fila nula. Las claves compuestas requieren comparar todas sus columnas y analizar la nulabilidad. Reparar relaciones mediante nombres descriptivos en lugar de la clave declarada puede crear asociaciones incorrectas.

Evaluar ambos sentidos del acceso

Antes de crear el índice ilustrado, inspecciona los existentes. Un índice compuesto que comience con CustomerId puede servir para la comprobación y las consultas. Si CustomerId aparece detrás de una clave independiente, normalmente no ofrece el mismo acceso. Examina planes de eliminaciones del padre y consultas importantes de los hijos.

Indexar todas las claves foráneas sin analizar la carga aumenta almacenamiento y escrituras. Por otro lado, no indexarlas porque casi no existen joins puede ignorar comprobaciones de eliminación y acciones en cascada. Mide ambas direcciones. Una cascada grande produce registro de transacciones y bloqueo incluso con índices apropiados.

La prueba final incluye insertar un hijo válido, intentar uno inválido en una transacción controlada y ejecutar una modificación representativa del padre en un entorno seguro. Confirma que la identidad de la aplicación recibe el error esperado y lo maneja correctamente. Confianza, integridad y rendimiento requieren evidencias distintas.

Conviene que el proceso de importación registre explícitamente el resultado de la validación y se detenga si falla. Un paso genérico de «habilitar restricciones» puede ocultar durante meses que el conjunto histórico nunca quedó comprobado.

Referencias técnicas: Microsoft Learn: Foreign keys · Microsoft Learn: Constraint trust.