Primär- und Fremdschlüssel-Einschränkungen

• 25.07.2017
• 9 Minuten zu lesen
• • s
  • M
  • M
  • c
  • J
  • +1

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL Database Azure SQL Managed Instance

Primärschlüssel und Fremdschlüssel sind zwei Arten von Einschränkungen, die zum Erzwingen der Datenintegrität in SQL Server-Tabellen verwendet werden können. Dies sind wichtige Datenbankobjekte.

Dieses Thema enthält die folgenden Abschnitte.

Primärschlüssel-Einschränkungen

Fremdschlüssel-Einschränkungen

Zugehörige Aufgaben

Primärschlüssel-Einschränkungen

Eine Tabelle hat normalerweise eine Spalte oder eine Kombination von Spalten, die Werte enthalten, die jede Zeile in der Tabelle eindeutig identifizieren. Diese Spalte oder Spalten werden als Primärschlüssel (PK) der Tabelle bezeichnet und erzwingen die Entitätsintegrität der Tabelle. Da Primärschlüssel-Beschränkungen eindeutige Daten garantieren, werden sie häufig auf einer Identitätsspalte definiert.

Wenn Sie eine Primärschlüssel-Beschränkung für eine Tabelle angeben, erzwingt die Datenbank-Engine die Eindeutigkeit der Daten, indem sie automatisch einen eindeutigen Index für die Primärschlüsselspalten erstellt. Dieser Index ermöglicht auch einen schnellen Zugriff auf Daten, wenn der Primärschlüssel in Abfragen verwendet wird. Wenn eine Primärschlüssel-Beschränkung für mehr als eine Spalte definiert ist, können Werte innerhalb einer Spalte dupliziert werden, aber jede Kombination von Werten aus allen Spalten in der Primärschlüssel-Beschränkungsdefinition muss eindeutig sein.

Wie in der folgenden Abbildung gezeigt, bilden die Spalten ProductID und VendorID in der Tabelle Purchasing.ProductVendor eine zusammengesetzte Primärschlüssel-Beschränkung für diese Tabelle. Dadurch wird sichergestellt, dass jede Zeile in der Tabelle „ProductVendor“ eine eindeutige Kombination aus „ProductID“ und „VendorID“ hat. Dies verhindert das Einfügen von doppelten Zeilen.

• Eine Tabelle kann nur eine Primärschlüssel-Beschränkung enthalten.

• Ein Primärschlüssel kann nicht mehr als 16 Spalten und eine Gesamtschlüssellänge von 900 Byte haben.

• Der durch eine Primärschlüssel-Beschränkung erzeugte Index kann nicht dazu führen, dass die Anzahl der Indizes in der Tabelle 999 nicht geclusterte Indizes und 1 geclusterten Index überschreitet.

• Wenn clustered oder nonclustered für eine Primärschlüssel-Beschränkung nicht angegeben ist, wird clustered verwendet, wenn es keinen clustered Index auf der Tabelle gibt.

• Alle Spalten, die innerhalb einer Primärschlüssel-Beschränkung definiert sind, müssen als nicht null definiert sein. Wenn nullability nicht angegeben ist, haben alle Spalten, die an einer Primärschlüssel-Beschränkung teilnehmen, ihre nullability auf not null gesetzt.

• Wenn ein Primärschlüssel auf einer CLR-benutzerdefinierten Typspalte definiert ist, muss die Implementierung des Typs binäre Ordnung unterstützen.

Fremdschlüssel-Beschränkungen

Ein Fremdschlüssel (FK) ist eine Spalte oder eine Kombination von Spalten, die verwendet wird, um eine Verknüpfung zwischen den Daten in zwei Tabellen herzustellen und zu erzwingen, um die Daten zu steuern, die in der Fremdschlüsseltabelle gespeichert werden können. Bei einem Fremdschlüsselverweis wird eine Verknüpfung zwischen zwei Tabellen hergestellt, wenn die Spalte oder Spalten, die den Primärschlüsselwert für eine Tabelle enthalten, von der Spalte oder den Spalten in einer anderen Tabelle referenziert werden. Diese Spalte wird zu einem Fremdschlüssel in der zweiten Tabelle.

Zum Beispiel hat die Tabelle Sales.SalesOrderHeader eine Fremdschlüsselverknüpfung mit der Tabelle Sales.SalesPerson, weil es eine logische Beziehung zwischen Kundenaufträgen und Verkäufern gibt. Die Spalte „SalesPersonID“ in der Tabelle „SalesOrderHeader“ entspricht der Primärschlüsselspalte der Tabelle „SalesPerson“. Die Spalte „SalesPersonID“ in der Tabelle „SalesOrderHeader“ ist der Fremdschlüssel zur Tabelle „SalesPerson“. Durch das Anlegen dieser Fremdschlüsselbeziehung kann ein Wert für SalesPersonID nicht in die Tabelle SalesOrderHeader eingefügt werden, wenn er nicht bereits in der Tabelle SalesPerson vorhanden ist.

Eine Tabelle kann maximal 253 andere Tabellen und Spalten als Fremdschlüssel referenzieren (ausgehende Referenzen). SQL Server 2016 (13.x) erhöht das Limit für die Anzahl der anderen Tabellen und Spalten, die auf Spalten in einer einzelnen Tabelle verweisen können (eingehende Verweise), von 253 auf 10.000. (Erfordert mindestens Kompatibilitätsstufe 130.) Die Erhöhung hat die folgenden Einschränkungen:

• Größere als 253 Fremdschlüsselreferenzen werden nur für DELETE-DML-Operationen unterstützt. UPDATE- und MERGE-Operationen werden nicht unterstützt.

• Eine Tabelle mit einer Fremdschlüsselreferenz auf sich selbst ist nach wie vor auf 253 Fremdschlüsselreferenzen beschränkt.

• Größere als 253 Fremdschlüsselreferenzen sind derzeit nicht für Columnstore-Indizes, speicheroptimierte Tabellen, Stretch Database oder partitionierte Fremdschlüsseltabellen verfügbar.

Indizes auf Fremdschlüssel-Beschränkungen

Im Gegensatz zu Primärschlüssel-Beschränkungen wird bei der Erstellung einer Fremdschlüssel-Beschränkung nicht automatisch ein entsprechender Index erstellt. Das manuelle Erstellen eines Indexes auf einem Fremdschlüssel ist jedoch aus folgenden Gründen oft sinnvoll:

• Fremdschlüsselspalten werden häufig in Join-Kriterien verwendet, wenn die Daten aus verwandten Tabellen in Abfragen kombiniert werden, indem die Spalte(n) in der Fremdschlüssel-Beschränkung einer Tabelle mit der/den Primär- oder Unique-Key-Spalte(n) in der anderen Tabelle abgeglichen werden. Ein Index ermöglicht es der Datenbank-Engine, verwandte Daten in der Fremdschlüsseltabelle schnell zu finden. Das Erstellen dieses Indexes ist jedoch nicht erforderlich. Daten aus zwei verwandten Tabellen können auch dann kombiniert werden, wenn keine Primärschlüssel- oder Fremdschlüssel-Beschränkungen zwischen den Tabellen definiert sind, aber eine Fremdschlüsselbeziehung zwischen zwei Tabellen zeigt an, dass die beiden Tabellen für die Kombination in einer Abfrage optimiert wurden, die die Schlüssel als ihre Kriterien verwendet.

• Änderungen an Primärschlüssel-Beschränkungen werden mit Fremdschlüssel-Beschränkungen in Bezugstabellen überprüft.

Referentielle Integrität

Obwohl der Hauptzweck einer Fremdschlüssel-Beschränkung darin besteht, die Daten zu kontrollieren, die in der Fremdschlüssel-Tabelle gespeichert werden können, kontrolliert sie auch Änderungen an Daten in der Primärschlüssel-Tabelle. Wenn beispielsweise die Zeile für einen Verkäufer aus der Tabelle „Sales.SalesPerson“ gelöscht wird und die ID des Verkäufers für Kundenaufträge in der Tabelle „Sales.SalesOrderHeader“ verwendet wird, ist die relationale Integrität zwischen den beiden Tabellen gebrochen; die Kundenaufträge des gelöschten Verkäufers sind in der Tabelle „SalesOrderHeader“ verwaist, ohne dass eine Verbindung zu den Daten in der Tabelle „SalesPerson“ besteht.

Eine Fremdschlüssel-Beschränkung verhindert diese Situation. Die Einschränkung erzwingt die referentielle Integrität, indem sie garantiert, dass keine Änderungen an den Daten in der Primärschlüsseltabelle vorgenommen werden können, wenn diese Änderungen die Verknüpfung zu den Daten in der Fremdschlüsseltabelle ungültig machen. Wenn versucht wird, die Zeile in einer Primärschlüsseltabelle zu löschen oder einen Primärschlüsselwert zu ändern, schlägt die Aktion fehl, wenn der gelöschte oder geänderte Primärschlüsselwert mit einem Wert in der Fremdschlüssel-Beschränkung einer anderen Tabelle übereinstimmt. Um eine Zeile in einer Fremdschlüsselbeschränkung erfolgreich zu ändern oder zu löschen, müssen Sie zuerst entweder die Fremdschlüsseldaten in der Fremdschlüsseltabelle löschen oder die Fremdschlüsseldaten in der Fremdschlüsseltabelle ändern, die den Fremdschlüssel mit anderen Primärschlüsseldaten verknüpft.

Kaskadierende referentielle Integrität

Durch die Verwendung von kaskadierenden referentiellen Integritätsbeschränkungen können Sie die Aktionen definieren, die die Datenbank-Engine ausführt, wenn ein Benutzer versucht, einen Schlüssel zu löschen oder zu aktualisieren, auf den vorhandene Fremdschlüssel zeigen. Die folgenden kaskadierenden Aktionen können definiert werden.

NO ACTION
Die Datenbank-Engine gibt einen Fehler aus und die Lösch- oder Aktualisierungsaktion für die Zeile in der übergeordneten Tabelle wird zurückgesetzt.

CASCADE
Zugehörige Zeilen werden in der referenzierenden Tabelle aktualisiert oder gelöscht, wenn diese Zeile in der übergeordneten Tabelle aktualisiert oder gelöscht wird. CASCADE kann nicht angegeben werden, wenn eine Zeitstempelspalte entweder Teil des Fremdschlüssels oder des referenzierten Schlüssels ist. ON DELETE CASCADE kann nicht für eine Tabelle angegeben werden, die einen INSTEAD OF DELETE-Trigger hat. ON UPDATE CASCADE kann nicht für Tabellen angegeben werden, die INSTEAD OF UPDATE-Trigger haben.

SET NULL
Alle Werte, die den Fremdschlüssel bilden, werden auf NULL gesetzt, wenn die entsprechende Zeile in der übergeordneten Tabelle aktualisiert oder gelöscht wird. Damit diese Einschränkung ausgeführt werden kann, müssen die Fremdschlüsselspalten nullbar sein. Kann nicht für Tabellen angegeben werden, die INSTEAD OF UPDATE-Trigger haben.

SET DEFAULT
Alle Werte, aus denen der Fremdschlüssel besteht, werden auf ihre Standardwerte gesetzt, wenn die entsprechende Zeile in der übergeordneten Tabelle aktualisiert oder gelöscht wird. Damit diese Einschränkung ausgeführt werden kann, müssen alle Fremdschlüsselspalten Standarddefinitionen haben. Wenn eine Spalte nullbar ist und kein expliziter Standardwert festgelegt ist, wird NULL der implizite Standardwert der Spalte. Kann nicht für Tabellen angegeben werden, die INSTEAD OF UPDATE-Trigger haben.

CASCADE, SET NULL, SET DEFAULT und NO ACTION können auf Tabellen kombiniert werden, die referenzielle Beziehungen zueinander haben. Wenn die Datenbank-Engine auf NO ACTION stößt, stoppt sie und rollt verwandte CASCADE-, SET NULL- und SET DEFAULT-Aktionen zurück. Wenn eine DELETE-Anweisung eine Kombination von CASCADE-, SET NULL-, SET DEFAULT- und NO ACTION-Aktionen verursacht, werden alle CASCADE-, SET NULL- und SET DEFAULT-Aktionen angewendet, bevor die Datenbank-Engine auf NO ACTION prüft.

Trigger und kaskadierende referenzielle Aktionen

Kaskadierende referenzielle Aktionen feuern die AFTER UPDATE- oder AFTER DELETE-Trigger auf folgende Weise aus:

• Alle kaskadierenden referenziellen Aktionen, die direkt durch das ursprüngliche DELETE oder UPDATE verursacht werden, werden zuerst ausgeführt.

• Wenn für die betroffenen Tabellen AFTER-Trigger definiert sind, werden diese Trigger nach der Ausführung aller kaskadierenden Aktionen ausgelöst. Diese Trigger werden in umgekehrter Reihenfolge der kaskadierenden Aktion ausgelöst. Wenn es mehrere Trigger für eine einzelne Tabelle gibt, werden sie in zufälliger Reihenfolge ausgelöst, es sei denn, es gibt einen dedizierten ersten oder letzten Trigger für die Tabelle. Diese Reihenfolge wird mit sp_settriggerorder festgelegt.

• Wenn mehrere kaskadierende Ketten von der Tabelle ausgehen, die das direkte Ziel einer UPDATE- oder DELETE-Aktion war, ist die Reihenfolge, in der diese Ketten ihre jeweiligen Trigger auslösen, nicht spezifiziert. Eine Kette feuert jedoch immer alle ihre Trigger ab, bevor eine andere Kette mit dem Feuern beginnt.

• Ein AFTER-Trigger auf der Tabelle, die das direkte Ziel einer UPDATE- oder DELETE-Aktion ist, feuert unabhängig davon, ob irgendwelche Zeilen betroffen sind. In diesem Fall sind keine anderen Tabellen von der Kaskadierung betroffen.

• Wenn einer der vorherigen Trigger UPDATE- oder DELETE-Operationen auf anderen Tabellen durchführt, können diese Aktionen sekundäre Kaskadierungsketten starten. Diese sekundären Ketten werden für jede UPDATE- oder DELETE-Operation nacheinander abgearbeitet, nachdem alle Trigger auf allen primären Ketten ausgelöst wurden. Dieser Prozess kann rekursiv für nachfolgende UPDATE- oder DELETE-Operationen wiederholt werden.

• Das Ausführen von CREATE-, ALTER-, DELETE- oder anderen Data Definition Language (DDL)-Operationen innerhalb der Trigger kann DDL-Trigger auslösen. Diese können anschließend DELETE- oder UPDATE-Operationen ausführen, die weitere kaskadierende Ketten und Trigger starten.

• Wenn innerhalb einer bestimmten kaskadierenden referenziellen Aktionskette ein Fehler erzeugt wird, wird ein Fehler ausgelöst, es werden keine AFTER-Trigger in dieser Kette ausgelöst und die DELETE- oder UPDATE-Operation, die die Kette erzeugt hat, wird zurückgesetzt.

• Eine Tabelle, die einen INSTEAD OF-Trigger hat, kann nicht auch eine REFERENCES-Klausel haben, die eine kaskadierende Aktion angibt. Ein AFTER-Trigger auf einer Tabelle, auf die eine kaskadierende Aktion abzielt, kann jedoch eine INSERT-, UPDATE- oder DELETE-Anweisung auf einer anderen Tabelle oder Ansicht ausführen, die einen INSTEAD OF-Trigger auslöst, der auf diesem Objekt definiert ist.

Verwandte Aufgaben

In der folgenden Tabelle sind die allgemeinen Aufgaben aufgeführt, die mit Primärschlüssel- und Fremdschlüssel-Einschränkungen verbunden sind.

Aufgabe	Thema
Beschreibt, wie man einen Primärschlüssel erstellt.	Primärschlüssel erstellen
Beschreibt, wie Sie einen Primärschlüssel löschen können.	Primärschlüssel löschen
Beschreibt, wie Sie einen Primärschlüssel ändern können.	Primärschlüssel modifizieren
Beschreibt, wie Sie Fremdschlüsselbeziehungen erstellen	Fremdschlüsselbeziehungen erstellen
Beschreibt, wie Sie Fremdschlüsselbeziehungen modifizieren.	Fremdschlüsselbeziehungen ändern
Beschreibt, wie man Fremdschlüsselbeziehungen löscht.	Fremdschlüsselbeziehungen löschen
Beschreibt, wie man Fremdschlüssel-Eigenschaften anzeigt.	Fremdschlüssel-Eigenschaften anzeigen
Beschreibt, wie Sie Fremdschlüssel-Beschränkungen für die Replikation deaktivieren können.	Fremdschlüssel-Beschränkungen für die Replikation deaktivieren
Beschreibt, wie Sie Fremdschlüssel-Beschränkungen während einer INSERT- oder UPDATE-Anweisung deaktivieren können.	Fremdschlüsselbeschränkungen bei INSERT- und UPDATE-Anweisungen deaktivieren