SQL Server-Fremdschlüssel: Indizes und vertrauenswürdige Prüfung

Ein Datenimport endet erfolgreich, nachdem Fremdschlüssel vorübergehend deaktiviert wurden. Die Anwendung arbeitet weiter, neue Einfügungen werden scheinbar geprüft. Monate später dauert das Löschen eines Kunden ungewöhnlich lange, und Berichte finden Bestellungen ohne Kunden. Hier wurden zwei Fragen vermischt: Sind vorhandene Daten validiert, und kann SQL Server die Beziehung effizient prüfen?

Ein Fremdschlüssel beschreibt eine Beziehung, erzeugt aber nicht automatisch einen Index auf den referenzierenden Spalten der Kindtabelle. Der Primärschlüssel oder eindeutige Schlüssel der Elterntabelle stellt nur den gültigen Bezugsschlüssel bereit. Bei Änderungen oder Löschungen auf der Elternseite muss SQL Server weiterhin passende Kindzeilen suchen.

Aktivierung und Vertrauen getrennt prüfen

Die folgende Abfrage läuft in der betroffenen Datenbank. Die Sichtbarkeit von Metadaten hängt von Berechtigungen ab. Eine leere Liste unter einem eingeschränkten Benutzer beweist deshalb nicht, dass keine problematischen Constraints existieren.

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;

Ein aktivierter Fremdschlüssel kann nicht vertrauenswürdig sein. Wird er ohne Prüfung alter Daten eingeschaltet, schützt er möglicherweise künftige Änderungen, lässt aber historische Verletzungen bestehen. Der Optimierer darf daraus nicht dieselben Annahmen ableiten wie aus einer vollständig validierten Beziehung. Ein erfolgreicher neuer INSERT ist daher kein Vertrauensnachweis.

Bei einem deaktivierten Constraint fehlt zusätzlich die betreffende Prüfung zukünftiger Schreibvorgänge. Halten Sie fest, warum die Deaktivierung erfolgte und welche Daten seitdem geändert wurden. Hat die Anwendung weitergearbeitet, reicht es nicht, ausschließlich die ursprünglich importierten Zeilen zu untersuchen.

Erst Daten korrigieren, dann validieren

Die nächsten Anweisungen sind ein anzupassendes Muster für vorhandene Übungstabellen dbo.Orders und dbo.Customers. Ersetzen Sie Namen erst nach Prüfung der echten Beziehung. Der Anti-Join findet nicht leere Kundenschlüssel ohne Elternzeile. Er entscheidet nicht, ob Bestellungen gelöscht, korrigiert oder fehlenden Kunden zugeordnet werden sollen.

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;

Die beiden CHECK haben unterschiedliche Aufgaben. WITH CHECK validiert vorhandene Daten; CHECK CONSTRAINT aktiviert den Constraint. Das kann umfangreiche Lesezugriffe und Sperren erfordern. Planen Sie diesen Aufwand und kontrollieren Sie anschließend is_disabled und is_not_trusted. Die tatsächlichen Katalogwerte sind zuverlässiger als die bloße Erinnerung, den Constraint eingeschaltet zu haben.

Ein nullable Fremdschlüssel darf NULL als fehlende Beziehung enthalten. Solche Werte sind nicht automatisch verwaiste Datensätze. Zusammengesetzte Fremdschlüssel müssen über sämtliche beteiligten Spalten geprüft werden. Eine vermeintliche Reparatur über Anzeigenamen statt über den definierten Schlüssel kann falsche Beziehungen erzeugen.

Indizes anhand beider Zugriffsrichtungen bewerten

Prüfen Sie vor dem zusätzlichen Index vorhandene Definitionen. Ein zusammengesetzter Index mit CustomerId als führender Spalte kann die Beziehung bereits ausreichend unterstützen. Steht CustomerId erst hinter einem unabhängigen Schlüssel, ist der Zugriff gewöhnlich nicht gleichwertig. Vergleichen Sie Pläne von Elternlöschungen und wichtigen Abfragen auf der Kindseite.

Jeden Fremdschlüssel pauschal zu indexieren erhöht Speicher- und Schreibaufwand. Umgekehrt übersieht die Aussage „Die Anwendung führt kaum Joins aus“ möglicherweise teure Löschprüfungen oder Kaskaden. Beide Richtungen zählen. Umfangreiche Kaskaden können trotz passender Indizes viel Logging und Blocking erzeugen, weil die Kindzeilen tatsächlich geändert werden.

Eine Abschlussprüfung umfasst eine gültige Kindzeile, eine absichtlich ungültige Kindzeile in einem kontrollierten Test und eine repräsentative Elternänderung. Verwenden Sie eine sichere Umgebung und prüfen Sie die Fehlerbehandlung der Anwendung. Vertrauen des Constraints, fachliche Richtigkeit und Zugriffskosten hängen zusammen, benötigen aber jeweils eigene Nachweise.

Lassen Sie insbesondere Importabläufe nicht lediglich „Constraint wieder aktivieren“ als letzten Schritt ausführen. Der Ablauf sollte das Ergebnis der Validierung dokumentieren und bei Fehlern stoppen. Andernfalls bleibt die Ausnahme unbemerkt über viele weitere Lieferungen hinweg bestehen und vergrößert den späteren Bereinigungsaufwand.

Technische Referenzen: Microsoft Learn: Foreign keys · Microsoft Learn: Constraint trust.