Microsoft SQL Server
Fremde Schlüssel

Fremdschlüsselbeziehung / Einschränkung

Mit Fremdschlüsseln können Sie die Beziehung zwischen zwei Tabellen definieren. Eine (übergeordnete) Tabelle muss über einen Primärschlüssel verfügen, der Zeilen in der Tabelle eindeutig identifiziert. Eine andere (untergeordnete) Tabelle kann in einer der Spalten den Wert des Primärschlüssels vom übergeordneten Element haben. Die FOREIGN KEY REFERENCES-Einschränkung stellt sicher, dass Werte in der untergeordneten Tabelle als Primärschlüsselwert in der übergeordneten Tabelle vorhanden sein müssen.

In diesem Beispiel haben wir eine übergeordnete Company-Tabelle mit dem Primärschlüssel CompanyId und eine untergeordnete Employee-Tabelle mit der ID der Firma, in der dieser Mitarbeiter arbeitet.

create table Company (
   CompanyId int primary key,
   Name nvarchar(200)
)
create table Employee (
    EmployeeId int,
    Name nvarchar(200),
    CompanyId int
        foreign key references Company(companyId)
)

Fremdschlüsselreferenzen stellen sicher, dass Werte, die in die Spalte Employee.CompanyId eingefügt werden, auch in der Spalte Company.CompanyId vorhanden sein müssen. Außerdem kann niemand die Firma in der Firmentabelle löschen, wenn mindestens ein Mitarbeiter mit einer passenden CompanyId in der Child-Tabelle vorhanden ist.

Durch die FOREIGN KEY-Beziehung wird sichergestellt, dass Zeilen in zwei Tabellen nicht "nicht verknüpft" werden können.

Beibehaltung der Beziehung zwischen übergeordneten / untergeordneten Zeilen

Nehmen wir an, wir haben eine Zeile in der Company-Tabelle mit companyId 1. Wir können eine Zeile in die Employee-Tabelle einfügen, die companyId 1 hat:

insert into Employee values (17, 'John', 1)

Wir können jedoch keinen Mitarbeiter einfügen, der über eine nicht vorhandene CompanyId verfügt:

insert into Employee values (17, 'John', 111111)

Meldung 547, Ebene 16, Status 0, Zeile 12 Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung "FK__Employee__Compan__1EE485AA". Der Konflikt ist in der Datenbank "MyDb", Tabelle "dbo.Company", Spalte "CompanyId" aufgetreten. Die Anweisung wurde beendet.

Außerdem können wir die übergeordnete Zeile in der Firmentabelle nicht löschen, solange in der Employee-Tabelle mindestens eine untergeordnete Zeile vorhanden ist.

delete from company where CompanyId = 1

Meldung 547, Ebene 16, Status 0, Zeile 14 Die DELETE-Anweisung steht in Konflikt mit der REFERENCE-Einschränkung "FK__Employee__Compan__1EE485AA". Der Konflikt ist in der Datenbank "MyDb", Tabelle "dbo.Employee", Spalte "CompanyId" aufgetreten. Die Anweisung wurde beendet.

Durch die Fremdschlüsselbeziehung wird sichergestellt, dass die Zeilen "Unternehmen" und "Mitarbeiter" nicht "getrennt" werden.

Fremdschlüsselbeziehung für vorhandene Tabelle hinzufügen

Die FOREIGN KEY- Einschränkung kann für vorhandene Tabellen hinzugefügt werden, die noch nicht in Beziehung stehen. Stellen Sie sich vor, wir haben Tabellen für Unternehmen und Mitarbeiter, in denen die Spalte CompanyId der Tabelle Mitarbeiter aufgeführt ist, aber keine Fremdschlüsselbeziehung haben. Mit der Anweisung ALTER TABLE können Sie eine Fremdschlüsseleinschränkung für eine vorhandene Spalte hinzufügen, die auf andere Tabellen und Primärschlüssel in dieser Tabelle verweist:

alter table Employee
    add  foreign key (CompanyId) references Company(CompanyId)

Fügen Sie einen Fremdschlüssel für eine vorhandene Tabelle hinzu

FOREIGN KEY- Spalten mit Einschränkung können zu vorhandenen Tabellen hinzugefügt werden, die noch nicht in Beziehung stehen. Stellen Sie sich vor, wir haben Firmen- und Mitarbeiter-Tabellen, in denen die Employee-Tabelle keine CompanyId-Spalte hat. Mit der Anweisung ALTER TABLE können Sie eine neue Spalte mit Fremdschlüsseleinschränkung hinzufügen, die auf eine andere Tabelle und einen Primärschlüssel in dieser Tabelle verweist:

alter table Employee
    add CompanyId int foreign key references Company(CompanyId)

Informationen über Fremdschlüsseleinschränkungen erhalten

Die Systemansicht sys.foreignkeys gibt Informationen zu allen Fremdschlüsselbeziehungen in der Datenbank zurück:

select name,
 OBJECT_NAME(referenced_object_id) as [parent table],
 OBJECT_NAME(parent_object_id) as [child table],
 delete_referential_action_desc,
 update_referential_action_desc
from sys.foreign_keys