Freigeben über

  • Artikel

Kaskadierende Einschränkungen der referenziellen Integrität

Durch Verwendung von Einschränkungen der referenziellen Integrität können Sie die Aktionen definieren, die SQL Server durchführt, wenn ein Benutzer versucht, einen Schlüssel zu löschen oder zu aktualisieren, auf den Fremdschlüssel zeigen.

Die REFERENCES-Klauseln der CREATE TABLE- und ALTER TABLE-Anweisungen unterstützen die ON DELETE- und ON UPDATE-Klauseln: Kaskadierende Aktionen können auch mithilfe des Dialogfeldes Fremdschlüsselbeziehungen definiert werden:

  • [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

  • [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

NO ACTION ist der Standardwert, falls ON DELETE oder ON UPDATE nicht angegeben wird.

  • ON DELETE NO ACTION
    Wenn versucht wird, eine Zeile mit einem Schlüssel zu löschen, auf den von Fremdschlüsseln in Zeilen anderer Tabellen verwiesen wird, wird ein Fehler ausgelöst, und für die DELETE-Anweisung wird ein Rollback ausgeführt.

  • ON UPDATE NO ACTION
    Wenn versucht wird, eine Zeile mit einem Schlüssel zu aktualisieren, auf den von Fremdschlüsseln in Zeilen anderer Tabellen verwiesen wird, wird ein Fehler ausgelöst, und für die UPDATE-Anweisung wird ein Rollback ausgeführt.

CASCADE, SET NULL und SET DEFAULT ermöglichen, dass sich das Löschen oder Aktualisieren von Schlüsselwerten auf die definierten Tabellen auswirkt, damit Fremdschlüsselbeziehungen bis zu der Tabelle zurückverfolgt werden können, in der die Änderung vorgenommen wurde. Falls kaskadierende referenzielle Aktionen auch in den Zieltabellen definiert wurden, werden die angegebenen kaskadierenden Aktionen ebenfalls für die gelöschten oder aktualisierten Zeilen verwendet. CASCADE ist für Fremd- oder Primärschlüssel, die eine timestamp-Spalte aufweisen, nicht zulässig.

  • ON DELETE CASCADE
    Wenn versucht wird, eine Zeile mit einem Schlüssel zu löschen, auf den von Fremdschlüsseln in Zeilen anderer Tabellen verwiesen wird, werden alle Zeilen, die diese Fremdschlüssel enthalten, ebenfalls gelöscht.

  • ON UPDATE CASCADE
    Wenn versucht wird, eine Zeile mit einem Schlüssel zu aktualisieren, auf den von Fremdschlüsseln in Zeilen anderer Tabellen verwiesen wird, werden alle Werte, die den Fremdschlüssel bilden, ebenfalls auf den für den Schlüssel angegebenen neuen Wert aktualisiert.

    HinweisHinweis

    CASCADE kann nicht angegeben werden, wenn eine timestamp-Spalte Teil eines Fremdschlüssels oder des Schlüssels ist, auf den verwiesen wird.

  • ON DELETE SET NULL
    Wenn versucht wird, eine Zeile mit einem Schlüssel zu löschen, auf den von Fremdschlüsseln in Zeilen anderer Tabellen verwiesen wird, werden alle Werte, die den Fremdschlüssel bilden, in den Zeilen, auf die verwiesen wird, auf NULL gesetzt. Um diese Einschränkung ausführen zu können, müssen alle Fremdschlüsselspalten der Zieltabelle auf NULL gesetzt werden können.

  • ON UPDATE SET NULL
    Wenn versucht wird, eine Zeile mit einem Schlüssel zu aktualisieren, auf den von Fremdschlüsseln in Zeilen anderer Tabellen verwiesen wird, werden alle Werte, die den Fremdschlüssel bilden, in den Zeilen, auf die verwiesen wird, auf NULL gesetzt. Um diese Einschränkung ausführen zu können, müssen alle Fremdschlüsselspalten der Zieltabelle auf NULL gesetzt werden können.

  • ON DELETE SET DEFAULT
    Wenn versucht wird, eine Zeile mit einem Schlüssel zu löschen, auf den von Fremdschlüsseln in Zeilen anderer Tabellen verwiesen wird, werden alle Werte, die den Fremdschlüssel bilden, in den Zeilen, auf die verwiesen wird, auf ihren Standardwert gesetzt. Um diese Einschränkung ausführen zu können, muss für alle Fremdschlüsselspalten der Zieltabelle eine Standarddefinition vorhanden sein. Wenn eine Spalte auf NULL gesetzt werden kann und kein expliziter Standardwert festgelegt ist, erhält die Spalte den impliziten Standardwert NULL. Für alle von NULL abweichenden Werte, die infolge von ON DELETE SET DEFAULT festgelegt werden, müssen entsprechende Werte in der Primärtabelle vorhanden sein, um die Gültigkeit der Fremdschlüsseleinschränkung zu gewährleisten.

  • ON UPDATE SET DEFAULT
    Wenn versucht wird, eine Zeile mit einem Schlüssel zu aktualisieren, auf den von Fremdschlüsseln in Zeilen anderer Tabellen verwiesen wird, werden alle Werte, die den Fremdschlüssel bilden, in den Zeilen, auf die verwiesen wird, auf ihren Standardwert gesetzt. Um diese Einschränkung ausführen zu können, muss für alle Fremdschlüsselspalten der Zieltabelle eine Standarddefinition vorhanden sein. Wenn eine Spalte auf NULL gesetzt werden kann und kein expliziter Standardwert festgelegt ist, erhält die Spalte den impliziten Standardwert NULL. Für alle von NULL abweichenden Werte, die infolge von ON UPDATE SET DEFAULT festgelegt werden, müssen entsprechende Werte in der Primärtabelle vorhanden sein, um die Gültigkeit der Fremdschlüsseleinschränkung zu gewährleisten.

Nehmen Sie beispielsweise die FK_ProductVendor_Vendor_VendorID-Einschränkung für die Purchasing.ProductVendor-Tabelle in AdventureWorks2008R2. Diese Einschränkung richtet eine Fremdschlüsselbeziehung von der VendorID-Spalte in der ProductVendor-Tabelle zur VendorID-Primärschlüsselspalte in der Purchasing.Vendor-Tabelle ein. Wenn ON DELETE CASCADE für die Einschränkung angegeben wird, werden durch das Löschen der Zeile in Vendor, deren VendorID dem Wert 100 entspricht, auch die drei Zeilen in ProductVendor gelöscht, deren VendorID den Wert 100 hat. Falls ON UPDATE CASCADE für die Einschränkung angegeben wird, werden durch das Aktualisieren des VendorID-Wertes in der Vendor-Tabelle von 100 bis 155 auch die VendorID-Werte in den drei Zeilen in ProductVendor aktualisiert, deren VendorID-Werte derzeit 100 entsprechen.

ON DELETE CASCADE kann nicht für eine Tabelle angegeben werden, die einen INSTEAD OF-Trigger aufweist. Für Tabellen, die INSTEAD OF UPDATE-Trigger aufweisen, können folgende Einschränkungen nicht angegeben werden: ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL und ON UDATE SET DEFAULT.

Mehrere kaskadierende Aktionen

Einzelne DELETE- oder UPDATE-Anweisungen können eine Reihe von kaskadierenden referenziellen Aktionen auslösen. Angenommen, eine Datenbank enthält die drei Tabellen TableA, TableB und TableC. Ein Fremdschlüssel in TableB ist mit ON DELETE CASCADE für den Primärschlüssel in TableA definiert. Ein Fremdschlüssel in TableC ist mit ON DELETE CASCADE für den Primärschlüssel in TableB definiert. Wenn eine DELETE-Anweisung Zeilen in TableA löscht, werden auch alle Zeilen in TableB gelöscht, die Fremdschlüssel aufweisen, die mit den gelöschten Primärschlüsseln in TableA übereinstimmen; anschließend werden alle Zeilen in TableC gelöscht, die Fremdschlüssel aufweisen, die mit den gelöschten Primärschlüsseln in TableB übereinstimmen.

Die kaskadierenden referenziellen Aktionen, die von einer DELETE- oder UPDATE-Anweisung ausgelöst werden, müssen eine Struktur ohne Zirkelverweise darstellen. Keine Tabelle darf mehr als einmal in der Liste aller kaskadierenden referenziellen Aktionen aufgeführt sein, die sich aus der DELETE- oder UPDATE-Anweisung ergeben. Außerdem darf die Struktur der kaskadierenden referenziellen Aktionen nicht mehr als einen Pfad zu jeder angegebenen Tabelle aufweisen. Jeder Zweig der Struktur wird beendet, wenn eine Tabelle angetroffen wird, für die NO ACTION angegeben wurde oder für die dies der Standardwert ist.

Trigger und kaskadierende referenzielle Aktionen

Kaskadierende referenzielle Aktionen lösen AFTER UPDATE- oder AFTER DELETE-Trigger in der folgenden Weise aus:

Alle kaskadierenden referenziellen Aktionen, die direkt von der ursprünglichen DELETE- oder UPDATE-Anweisung ausgelöst werden, werden zuerst ausgeführt.

Wenn für die betroffenen Tabellen AFTER-Trigger definiert wurden, werden diese Trigger ausgelöst, nachdem alle kaskadierenden Aktionen ausgeführt wurden. Diese Trigger werden in umgekehrter Reihenfolge zur kaskadierenden Aktion ausgelöst. Falls es mehrere Trigger für eine einzelne Tabelle gibt, werden diese in zufälliger Reihenfolge ausgelöst, sofern es für die Tabelle nicht einen dedizierten ersten oder letzten Trigger gibt. Diese Reihenfolge wird mithilfe von sp_settriggerorder angegeben.

Wenn mehrere kaskadierende Ketten ihren Ursprung in der Tabelle haben, die das direkte Ziel der UPDATE- oder DELETE-Aktion war, wird die Reihenfolge, in der diese Ketten ihre jeweiligen Trigger auslösen, nicht angegeben. Allerdings löst immer erst eine Kette alle ihre Trigger aus, bevor eine andere Kette die Auslösung startet.

Ein AFTER-Trigger für die Tabelle, die das direkte Ziel einer UPDATE- oder DELETE-Aktion ist, wird unabhängig davon ausgelöst, ob davon irgendwelche Zeilen betroffen sind oder nicht. In diesem Fall sind keine weiteren Tabellen von der Kaskadierung betroffen.

Wenn einer der vorherigen Trigger UPDATE- oder DELETE-Operationen für andere Tabellen ausführt, können diese Aktionen sekundäre kaskadierende Ketten starten. Die Verarbeitung dieser sekundären Ketten erfolgt für jede UPDATE- oder DELETE-Operation zu einem Zeitpunkt, nachdem alle Trigger für alle primären Ketten ausgelöst wurden. Dieser Prozess kann für alle nachfolgenden UPDATE- oder DELETE-Operationen rekursiv wiederholt werden.

Die Ausführung von CREATE-, ALTER-, DELETE- oder anderen Operationen in der Datendefinitionssprache (DDL, Data Definition Language) können zur Auslösung von DDL-Triggern führen. Das kann schließlich zur Ausführung von DELETE- oder UPDATE-Operationen führen, die zusätzliche kaskadierende Ketten und Trigger starten.

Tritt innerhalb einer bestimmten kaskadierenden referenziellen Aktionskette ein Fehler auf, wird ein Fehler ausgelöst, in dieser Kette werden keine AFTER-Trigger ausgelöst, und für die DELETE- oder UPDATE-Operation, mit der die Kette erzeugt wurde, wird ein Rollback ausgeführt.

Eine Tabelle mit einem INSTEAD OF-Trigger kann nicht gleichzeitig eine REFERENCES-Klausel aufweisen, die eine kaskadierende Aktion angibt. Ein AFTER-Trigger in einer Tabelle, die das Ziel einer kaskadierenden Aktion ist, kann jedoch eine INSERT-, UPDATE- oder DELETE-Anweisung in einer anderen Tabelle oder Sicht ausführen, die einen für dieses Objekt definierten INSTEAD OF-Trigger auslöst.

Kataloginformationen für kaskadierende referenzielle Einschränkungen

Das Abfragen der sys.foreign_keys-Katalogsicht gibt die folgenden Werte zurück, die Auskunft geben über die für einen Fremdschlüssel angegebene kaskadierende referenzielle Einschränkung geben.

Wert

Beschreibung

0

NO ACTION

1

CASCADE

2

SET NULL

3

SET DEFAULT

Die von sp_fkeys und sp_foreignkeys zurückgegebenen UPDATE_RULE- und DELETE_RULE-Spalten geben 0 zurück, wenn CASCADE, SET NULL oder SET DEFAULT angegeben ist, und geben 1 zurück, wenn NO ACTION angegeben oder der Standardwert ist..

Wenn ein Fremdschlüssel als Objekt von sp_help angegeben wird, enthält das Ausgaberesultset die folgenden Spalten.

Spaltenname

Datentyp

Beschreibung

delete_action

nvarchar(9)

Zeigt an, ob der Löschvorgang CASCADE, SET NULL; SET DEFAULT, NO ACTION oder N/V (nicht anwendbar) ist.

update_action

nvarchar(9)

Zeigt an, ob der Aktualisierungsvorgang CASCADE, SET NULL; SET DEFAULT, NO ACTION oder N/V (nicht anwendbar) ist.