Referenzielle Integrität

DBMS kann die Datenintegrität einer Datenbank gewährleisten

Was das im Einzelnen bedeutet, soll an folgendem Beispiel erläutert werden:

Die referenzielle Integrität (auch Beziehungsintegrität) wird bei folgenden Aktionen zerstört:

• Löschen eines Datensatzes in der Primärtabelle, obwohl noch eine Referenz in der Detailtabelle existiert,
• Löschen der Primärtabelle ohne zuvor die Detailtabelle zu löschen oder zu leeren,
• Hinzufügen eines Datensatzes in der Detailtabelle mit einer nicht existierenden Referenz,
  
• Änderung des Primär- oder des Fremdschlüssels, ohne diese Änderung einer gültigen Referenz entspricht.

tblAbteilung (Primärtabelle)

AbteilungId	Bezeichnung
1	Personal
2	Einkauf
3	Verkauf

tblMitarbeiter (Detailtabelle)

AbteilungId	Name
1	Lorenz
2	Hohl
1	Willschrein
3	Richter
2	Wiesenland

Aktivierung der referenziellen Integrität

Die referenzielle Integrität kann durch das DBMS gewahrt werden, wenn diese aktiviert wird.
Das kann bei der Erzeugung der Detailtabelle geschehen (Primärtabelle muss bereits existieren):

1. Durch die Deklaration eine Referenz bei entsprechenden Spalte

Definition der Spalte

REFERENCES Primärtabelle (Spaltenname)

[Optionen]

CREATE TABLE tblMitarbeiter
(
	AbteilungId int REFERENCES tblAbteilung(AbteilungId),
	Name varchar(50)
);

2. In der Liste der Einschränkungen (engl. Constraints)

[CONSTRAINT Constraint-Name]

FOREIGN KEY (Spaltenname)

REFERENCES Primärtabelle (Spaltenname)

[Optionen]

CREATE TABLE tblMitarbeiter
(
	AbteilungId int,
	Name varchar(50),
	CONSTRAINT fkAbteilungMitarbeiter FOREIGN KEY (AbteilungId) REFERENCES tblAbteilung(AbteilungId)
);

(Anmerkung: Es gibt noch andere SQL-Constraints)

3. Referenzielle Integrität kann auch nachträglich aktiviert werden:

ALTER TABLE Detailtabelle

ADD [CONSTRAINT Constraint-Name]

FOREIGN KEY (Spaltenname)

REFERENCES Primärtabelle (Spaltenname)

[Optionen]

ALTER TABLE tblMitarbeiter
  ADD CONSTRAINT fkAbteilungMitarbeiter FOREIGN KEY (AbteilungId) REFERENCES tblAbteilung(AbteilungId)

Nachteil

Bei großen Datenmengen Performanceverlust durch umfangreiche Prüfungen bei Lösch-, Änderungs- und Einfügeabfragen!
Lösungsmöglichkeit: Bei Entwicklung der Anwendung referenzielle Integrität einschalten, in der Produktionsumgebung referenzielle Integrität abschalten.

Änderungs- und Löschweitergabe

Optional kann bei der Aktivierung der referenziellen Integrität gleichzeitig auch die Änderungs- und/oder Löschweitergabe aktiviert werden.

Bei aktivierter Änderungsweitergabe, wird bei Änderung eines Primärschlüssels dieser automatisch auch in den referenzierenden Tabellen entsprechend geändert. (Dies spielt gelegentlich bei der Verwendung natürlicher Schlüssel (z.B. Familienname) eine Rolle. Werden künstliche Schlüssel (Id's) verwendet, werden diese normalerweise nicht geändert.

Löschweitergabe heißt, dass das Löschen des Primärdatensatzes nicht abgelehnt wird, sondern dass abhängige Datensätze in Detailtabellen automatisch mitgelöscht werden.

ALTER TABLE tblMitarbeiter
  ADD CONSTRAINT fkAbteilungMitarbeiter FOREIGN KEY (AbteilungId) REFERENCES tblAbteilung(AbteilungId)
  ON UPDATE CASCADE
  ON DELETE CASCADE

Abhängig vom DBMS können verschiedene Optionen verwendet werden (hier nur MS-SQL-Server):

• cascade – Weitergabe der Änderung an die Detailtabelle
• no action – alle Änderungen werden verweigert (Voreinstellung)
• set null - Änderung des Verweises in der Detailtabelle auf NULL, wenn die betreffende Spalte NULL-Werte zulässt, also nicht mit NOT NULL definiert wurde (ab Version 2005)
• set default - Änderung des Verweises in der Detailtabelle auf den Vorgabewert der Spalte, wenn für die betreffende Spalte ein DEFAULT-Wert festgelegt ist (ab Version 2005)