EINSCHRÄNKUNG: Datenbankintegrität

Kontrolle der Datenbankintegrität

Eine weitere wichtige Sache, die Sie über Datenbanken wissen sollten, sind EINSCHRÄNKUNGEN. Mithilfe von Einschränkungen können Sie Datenänderungen in Ihren Tabellen steuern und deren Integrität und Konsistenz wahren.

Was ist Datenkonsistenz, wenn wir von einer Datenbank sprechen?

Nehmen wir unseren Online-Shop mit Mitarbeiter-, Produkt- und Aufgabentabellen . Wir wissen bereits, dass es in der Aufgabentabelle Aufgaben geben kann, die niemandem zugewiesen sind: Die Employee_ID solcher Zeilen ist NULL.

Aber was passiert, wenn es in der Aufgabentabelle einen Eintrag mit einer Employee_ID von beispielsweise 115 gibt? Einen solchen Mitarbeiter haben wir schließlich nicht. Wir haben keinen Mitarbeiter mit der ID = 115 in der Mitarbeitertabelle. Gleichzeitig befindet sich in der Aufgabentabelle ein Link zu einem Mitarbeiter mit dieser ID. Dies ist ein Beispiel für Dateninkonsistenz .

Wie gleichen wir diese Daten ab? Im Idealfall wäre es so, dass der SQL-Server bei jeder Datenänderung alle diese Nuancen kontrolliert. Und es gibt eine solche Möglichkeit, sie heißt FOREIGN_KEY.

Wenn eine Spalte in Ihrer Tabelle nicht nur Zahlen, sondern auch ID-Zeilen aus einer anderen Tabelle enthält, kann dies explizit angegeben werden.

Hinzufügen eines Fremdschlüssels

Ein solcher Schlüssel kann der Tabelle sowohl bei der Erstellung als auch danach mit ALTER TABLE hinzugefügt werden. Das Format unterscheidet sich nicht grundsätzlich. Wir stellen beide Möglichkeiten vor.

Die allgemeine Form eines solchen Schlüssels/einer solchen Regel ist:

FOREIGN KEY (column)
  	REFERENCES table(column)

Fügen wir diesen Schlüssel/diese Regel zur Aufgabentabelle hinzu , um sicherzustellen, dass alle Mitarbeiter-IDs aus der Tabelle auf einen vorhandenen Eintrag in der Mitarbeitertabelle verweisen. Dieses Skript sieht folgendermaßen aus:

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

Und wenn wir uns dazu entschließen würden, diese Regel zum Zeitpunkt der Erstellung der Aufgabentabelle hinzuzufügen, dann würde der Code so aussehen:

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

Übrigens gibt es Situationen, in denen die Zeichenfolge, auf die wir uns beziehen, einen eindeutigen zusammengesetzten Schlüssel hat: zum Beispiel „Name und Geburtsjahr“ oder „productCatogoryId und productId“. Dann kann FOREIGN KEY wie folgt geschrieben werden:

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

AUSLÄNDISCHER SCHLÜSSEL und sich ändernde Daten

Stellen Sie sich nun eine Situation vor, in der wir beschlossen haben, einige Daten in der Mitarbeitertabelle zu aktualisieren, und sich unsere Mitarbeiter-ID geändert hat. Was passiert mit den Daten in der Aufgabentabelle? Das ist richtig, sie werden irrelevant und die Integrität unserer Datenbank wird verletzt.

Um dies zu verhindern, können Sie SQL Server anweisen, die Mitarbeiter-ID aller Zeilen in allen Tabellen zu ändern, die auf diese bestimmte geänderte ID verweisen, wenn sich die ID in der Mitarbeitertabelle ändert.

Solche Skripte heißen OnUpdate und OnDelete . Was ist zu tun, wenn sich die Datensatz-ID ändert, und was ist zu tun, wenn der Datensatz gelöscht wird?

Bei der Entfernung ist nicht alles so einfach. Wenn Sie abhängige Objekte in der Datenbank haben, die durch aufeinander verweisende Zeichenfolgen dargestellt werden, sind beim Löschen eines Objekts verschiedenste Verhaltensszenarien möglich.

Nehmen wir an, wir löschen einen Site-Benutzer, was bedeutet, dass wir seine gesamte persönliche Korrespondenz löschen müssen. Aber es ist unwahrscheinlich, dass wir alle seine öffentlichen Kommentare entfernen sollten.

Oder ein Mitarbeiter kündigt. Es wäre seltsam, wenn er kündigen würde und gleichzeitig alle ihm zugewiesenen Aufgaben aus der Datenbank verschwinden würden. Wären sie aber nicht von ihm ernannt geblieben, wäre es auch schlecht ausgegangen. Es ist richtiger, es so zu gestalten, dass der Mitarbeiter kündigen kann, nachdem er alle seine Aufgaben anderen Personen zugewiesen hat.

So können wir diese Szenarien mit FOREIGN KEY beschreiben. Die allgemeine Form eines solchen Schlüssels/einer solchen Regel ist:

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

Was ist zu tun, wenn Datensätze gelöscht (ON DELETE) oder geändert (ON UPDATE) werden? Insgesamt kann es für den SQL-Server in jeder dieser Situationen 5 Möglichkeiten geben, zu agieren:

So könnten wir unsere Aufgabentabelle ändern:

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

Was steht hier geschrieben:

ON UPDATE CASCADE : Wenn sich der ID-Schlüssel in der Mitarbeitertabelle ändert, ändern Sie auch die Mitarbeiter-ID in der Aufgabentabelle, die darauf verweist.

ON DELETE RESTRICT : Wenn eine Zeile aus der Mitarbeitertabelle gelöscht wird und in der Aufgabentabelle referenziert wird, verhindern Sie, dass die Zeile aus der Mitarbeitertabelle gelöscht wird.