Manchmal sieht man den Wald vor lauter Bäumen nicht, wenn man etwas "zwischen Tür und Angel" codiert. Daher dieser kleine Beitrag. Vielleicht hilft er dem einen oder anderen ja, die Zeit für die Fehlersuche zu sparen.

Vor ein paar Tagen habe ich ziemlich fassungslos eine geraume Zeit versucht, eine FOREIGN KEY Einschränkung zwischen 2 existierenden Tabellen zu erstellen. Dabei kam immer wieder die Fehlermeldung:

Msg 547, Level 16, State 0, Line 21
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Child_Parent". The conflict occurred in database "tempdb", table "dbo.Parent", column 'ParentID'.

Der erste Blick ging natürlich zum entsprechenden SQL-Statement:

ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID) REFERENCES dbo.Parent (ParentID);

Das ist jedoch syntaktisch einwandfrei und konnte nicht der Grund für den Fehler sein. Was also dann?

Nun, nachdem auch der 2.te und 3.te Versuch, das Skript laufen zu lassen, erfolglos endete und mittlerweile bestimmt 10 Minuten verbraucht waren, bin ich endlich auf die naheliegende Idee gekommen, die Fehlermeldung genauer zu lesen und daraufhin die Daten in den beteiligten Tabellen etwas näher zu untersuchen. Dabei kam dann raus, dass einige dieser Daten die FK-Einschränkung verletzen würden und SQL Server aus diesem Grund die Fehlermeldung ausgibt. Nachdem die Daten einmal korrigiert waren, lief das Skript fehlerfrei durch und die FK-Einschränkung konnte problemlos erstellt werden.

Was aber, wenn man die Daten nicht korrigieren kann oder will?

Nun, in diesem Fall kann man kann man die FK-Einschränkung trotzdem erstellen und zwar, in dem man den NOCHECK Zusatz verwendet:

ALTER TABLE dbo.Child WITH NOCHECK ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID) REFERENCES dbo.Parent (ParentID);

Sofern der Benutzer nichts anderes angibt, benutzt SQL Server  WITH CHECK für neu erstellte Einschränkungen.  Durch die Angabe von WITH NOCHECK überprüft SQL Server nicht die Gültigkeit der existierenden Daten bezüglich der Einschränkung , sondern nur für neu hinzugefügte Daten. Das mag zwar für den Augenblick okay sein, aber langfristig muss man sich bewusst sein, dass man die Glaubwürdigkeit der Datenbank aufs Spiel setzt, da man Daten in seinen Tabellen hat, welche nicht auf ihre Konformität mit den bestehenden Regeln überprüft wurden. SQL Server kennzeichnet solche Einschränkung und man kann sie jederzeit folgendermassen ausfindig machen:

SELECT
    name,
    is_not_trusted
FROM
    sys.foreign_keys
WHERE
    is_not_trusted = 1

Doch es sprechen noch mehr Gründe dafür, FK-Einschränkungen direkt bei der Erstellung überprüfen zu lassen. Der Optimierer ignoriert diese Einschränkung solange, bis sie verifiziert wurden. Bei nicht verifizierten Einschränkungen kann dies also durchaus zu einem suboptimalen Ausführungsplan führen und damit schlechterer Performance führen.

Nachtrag 21.10.2010: Wie es der Zufall will, kommt gerade dieser Artikel über den RSS-Reader: http://www.sqlservercentral.com/articles/Performance/71264/, der den Vorteil einer verifizierten FK-Einschränkung gut demonstriert.