Beispielerklärung von MySQL-Fremdschlüsseleinschränkungen

Die Fremdschlüsseleinschränkung von MySQL dient zum Herstellen einer Verknüpfung zwischen zwei Tabellen. Ändert sich eine Tabelle, ändert sich auch die andere Tabelle. Diese Funktion dient hauptsächlich dazu, die Konsistenz und Integrität der Tabellendaten sicherzustellen.
Bei zwei Tabellen, die durch Fremdschlüssel verknüpft sind, ist die Tabelle, in der sich der Primärschlüssel im verknüpften Feld befindet, die Primärtabelle (auch übergeordnete Tabelle genannt), und die Tabelle, in der sich der Fremdschlüssel befindet, ist die Sekundärtabelle (auch untergeordnete Tabelle genannt). Beim Definieren von Fremdschlüsseln müssen mehrere Regeln beachtet werden:

1. Die übergeordnete Tabelle muss bereits in der Datenbank vorhanden sein oder die Tabelle sein, die gerade erstellt wird. Im letzteren Fall sind die übergeordnete und die untergeordnete Tabelle dieselbe Tabelle. Eine solche Tabelle wird als selbstreferenzierende Tabelle bezeichnet, und diese Struktur wird als selbstreferenzierend bezeichnet.
2. Für die übergeordnete Tabelle muss ein Primärschlüssel definiert werden.
3. Der Primärschlüssel darf keine Nullwerte enthalten, in Fremdschlüsseln sind jedoch Nullwerte zulässig. Das heißt, solange jeder von Null verschiedene Wert des Fremdschlüssels im angegebenen Primärschlüssel vorkommt, ist der Inhalt dieses Fremdschlüssels korrekt.
4. Die Anzahl der Spalten im Fremdschlüssel muss mit der Anzahl der Spalten im Primärschlüssel der übergeordneten Tabelle übereinstimmen.
5. Der Datentyp der Spalte im Fremdschlüssel muss mit dem Datentyp der entsprechenden Spalte im Primärschlüssel der übergeordneten Tabelle übereinstimmen. Das ist alles eher allgemein gehalten, schauen wir uns also einige Beispiele an.

mysql:yeyztest ::>>Tabelle erstellen fk_test_1( 
 -> ID int nicht null Primärschlüssel auto_increment,
 -> Name varchar() Standard '');
Abfrage OK, Zeilen betroffen (0,10 Sek.)

mysql:yeyztest ::>>Tabelle erstellen fk_test_2(
 -> ID int nicht null Primärschlüssel auto_increment,
 -> uid int, 
 -> Fremdschlüssel fk_uid(uid) verweist auf fk_test_1(id));
Abfrage OK, Zeilen betroffen (0,06 Sek.)

Hier erstellen wir zwei Tabellen, eine ist fk_test_1 und die andere ist fk_test_2. Ein Fremdschlüssel wird auf die uid-Spalte von fk_test_2 gesetzt, um die id-Spalte der Tabelle von fk_test_1 zu verknüpfen. Hier ist es offensichtlich, dass fk_test_1 die übergeordnete Tabelle und fk_test_2 die untergeordnete Tabelle ist. Als Nächstes führen wir ein Dateneinfügungsexperiment durch.

mysql:yeyztest ::>>in fk_test_1 Werte einfügen (,'aaa'),(,'bbb');
Abfrage OK, Zeilen betroffen (0,00 Sek.)
Datensätze: Duplikate: Warnungen: 

mysql:yeyztest ::>>wählen Sie * aus fk_test_1;
+----+------+
| Ich würde | Name |
+----+------+
| | aaa |
| | bbb |
+----+------+
 Zeilen im Set (0,00 Sek.)

mysql:yeyztest ::>>in fk_test_2 Werte einfügen (,),(,);
Abfrage OK, Zeilen betroffen (0,00 Sek.)
Datensätze: Duplikate: Warnungen: 

mysql:yeyztest ::>>in fk_test_2 Werte einfügen (,);
Abfrage OK, Zeile betroffen (0,00 Sek.)


mysql:yeyztest ::>>in fk_test_2 Werte einfügen (,);  
FEHLER (): Eine untergeordnete Zeile kann nicht hinzugefügt oder aktualisiert werden: eine Fremdschlüsseleinschränkung schlägt fehl (`yeyztest`.`fk_test_2`, EINSCHRÄNKUNG `fk_test_2_ibfk_1` FREMDER SCHLÜSSEL (`uid`) REFERENZEN `fk_test_1` (`id`))

Fügen Sie zunächst zwei Daten in die Haupttabelle ein, nämlich id=1 und id=2, und fügen Sie dann Daten in die untergeordnete Tabelle ein. Die untergeordnete Tabelle kann erfolgreich Daten mit uid=1 und uid=2 einfügen, aber beim Einfügen von Daten mit uid=3 schlägt dies fehl. Das heißt, beim Einfügen in die untergeordnete Tabelle muss der eingefügte, dem Fremdschlüssel zugeordnete Feldwert standardmäßig der Wert sein, der in der zugeordneten Spalte der übergeordneten Tabelle enthalten ist. Beachten Sie hierbei die Standardsituation, die später erläutert wird.

Schauen wir uns die Löschsituation an.

mysql:yeyztest ::>>wählen Sie * aus fk_test_2;
+----+------+
| Ich würde | Benutzer-ID |
+----+------+
| | |
| | |
| | |
+----+------+
 Zeilen im Set (0,00 Sek.)

mysql:yeyztest ::>>löschen aus fk_test_2, wobei id=;
Abfrage OK, Zeile betroffen (0,00 Sek.)

mysql:yeyztest ::>>wählen Sie * aus fk_test_1;  
+----+------+
| Ich würde | Name |
+----+------+
| | aaa |
| | bbb |
+----+------+
 Zeilen im Set (0,00 Sek.)

mysql:yeyztest ::>>löschen aus fk_test_1, wobei id=; 
FEHLER (): Eine übergeordnete Zeile kann nicht gelöscht oder aktualisiert werden: Eine Fremdschlüsseleinschränkung schlägt fehl (`yeyztest`.`fk_test_2`, EINSCHRÄNKUNG `fk_test_2_ibfk_1` FREMDER SCHLÜSSEL (`uid`) REFERENZEN `fk_test_1` (`id`))

Es ist ersichtlich, dass beim Löschen der untergeordneten Tabelle fk_test_2 kein Problem auftritt, beim Löschen der übergeordneten Tabelle fk_test_1 zeigt sich jedoch, dass der Wert von id = 1 nicht gelöscht werden kann. Der Grund dafür ist, dass eine Fremdschlüsseleinschränkung vorliegt. Das heißt , beim Löschen aus der übergeordneten Tabelle kann der Spaltenwert, der bereits eine abhängige Zuordnung in der untergeordneten Tabelle aufweist, standardmäßig nicht direkt gelöscht werden. Beachten Sie hierbei die Voreinstellungen, welche im Folgenden erläutert werden.

Da das Löschen fehlgeschlagen ist, versuchen Sie es mit einer Aktualisierung.

mysql:yeyztest ::>>update fk_test_1 set id= wobei id=;   
FEHLER (): Eine übergeordnete Zeile kann nicht gelöscht oder aktualisiert werden: 
eine Fremdschlüsseleinschränkung schlägt fehl (`yeyztest`.`fk_test_2`, 
CONSTRAINT `fk_test_2_ibfk_1` Fremdschlüssel (`uid`) Referenzen `fk_test_1` (`id`))

mysql:yeyztest ::>>update fk_test_1 set name='ccc' wobei id=; 
Abfrage OK, Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: Geändert: Warnungen:

Es ist ersichtlich, dass die Aktualisierung der Primärschlüsselspalte der übergeordneten Tabelle immer noch nicht erfolgreich ausgeführt werden kann, die Aktualisierung anderer Spalten jedoch erfolgreich ausgeführt werden kann.

An diesem Punkt wissen wir bereits, dass die Existenz von Fremdschlüsseln dazu dient, die Integrität und Einheit der Daten sicherzustellen. Dies bringt jedoch auch ein kleines Problem mit sich: Alle Spalten in der übergeordneten Tabelle, die von der untergeordneten Tabelle abhängig sind, können nicht gelöscht werden. Das ist nicht das, was wir wollen. Einige Daten laufen tatsächlich ab und wir müssen sie löschen. Was sollen wir also jetzt tun?

Im obigen Test haben wir wiederholt ein Wort erwähnt, nämlich dass wir standardmäßig keine Lösch- und Aktualisierungsregeln für Fremdschlüssel festgelegt haben. Hier hat uns MySQL geholfen, die strengste Regel anzuwenden, nämlich „einschränken“. Tatsächlich gibt es noch einige andere Regeln, die hier alle aufgeführt sind:

• Löschen Sie die übergeordnete Tabelle:

kaskadieren, auf Null setzen, keine Aktion, einschränken

• Aktualisieren Sie die übergeordnete Tabelle:

kaskadieren, auf Null setzen, keine Aktion, einschränken

In

• „Einschränken“ ist die Standardoperation. Dies bedeutet, dass der übergeordneten Tabelle das Löschen oder Ändern der Fremdschlüsselspalten, von denen die untergeordnete Tabelle abhängt, verweigert wird. Dies ist die sicherste Einstellung.
• Kaskadierung bedeutet, dass die Datensätze in der untergeordneten Tabelle direkt gelöscht werden, wenn die übergeordnete Tabelle gelöscht wird. Dies ist die gefährlichste Einstellung.
• Auf Null setzen bedeutet, dass beim Löschen der übergeordneten Tabelle die untergeordnete Tabelle mit dem Nullwert verarbeitet wird.
• Keine Aktion bedeutet, dass beim Löschen der übergeordneten Tabelle keine Änderungen an der untergeordneten Tabelle vorgenommen werden.

Die Syntax zum Festlegen einer Zuordnung lautet wie folgt:

Tabelle ändern Tabellenname Einschränkung hinzufügen FK_ID Fremdschlüssel (Fremdschlüsselfeldname) Referenzen Fremdtabellenname (Primärschlüsselfeldname)
[beim Löschen {kaskadieren | auf null setzen | keine Aktion | einschränken}]
[bei Update {kaskadieren | auf null setzen | keine Aktion | einschränken}]

Testen wir nun die anderen drei Fälle, beginnend mit dem Kaskadenfall:

mysql:yeyztest ::>>wählen Sie * aus fk_test_1;
+----+------+
| Ich würde | Name |
+----+------+
| | ccc |
| | bbb |
+----+------+
 Zeilen im Set (0,00 Sek.)

mysql:yeyztest ::>>wählen Sie * aus fk_test_2;
+----+------+
| Ich würde | Benutzer-ID |
+----+------+
| | |
| | |
+----+------+
 Zeilen im Set (0,00 Sek.)

mysql:yeyztest ::>>zeige erstellen Tabelle fk_test_2\G
*************************** 1. Reihe ***************************
  Tabelle: fk_test_2
Tabelle erstellen: CREATE TABLE `fk_test_2` (
 `id` int() NICHT NULL AUTO_INCREMENT,
 `uid` int() DEFAULT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `fk_uid` (`uid`),
 EINSCHRÄNKUNG `fk_test_2_ibfk_1` FREMDER SCHLÜSSEL (`uid`) REFERENZEN `fk_test_1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8
 Zeile im Satz (0,00 Sek.)

mysql:yeyztest ::>>Tabelle ändern, fk_test_2, Fremdschlüssel löschen, fk_test_2_ibfk_1;
Abfrage OK, Zeilen betroffen (0,02 Sek.)
Datensätze: Duplikate: Warnungen: 

mysql:yeyztest ::>>Tabelle fk_test_2 ändern, Einschränkung fk_uid hinzufügen, Fremdschlüssel (uid) verweist beim Löschen der Kaskade auf fk_test_1(id);
Abfrage OK, Zeilen betroffen (0,03 Sek.)
Datensätze: Duplikate: Warnungen: 

#######################################
####Löschen Sie hier den Datensatz der übergeordneten Tabelle id= und überprüfen Sie das Ergebnis der untergeordneten Tabelle###
#######################################
mysql:yeyztest ::>>löschen aus fk_test_1, wobei id=;
Abfrage OK, Zeile betroffen (0,00 Sek.)

mysql:yeyztest ::>>wählen Sie * aus fk_test_1;
+----+------+
| Ich würde | Name |
+----+------+
| | ccc |
+----+------+
 Zeile im Satz (0,00 Sek.)

mysql:yeyztest ::>>wählen Sie * aus fk_test_2;
+----+------+
| Ich würde | Benutzer-ID |
+----+------+
| | |
+----+------+
 Zeile im Satz (0,00 Sek.)

Es ist ersichtlich, dass die Werte der übergeordneten Tabelle zu Beginn die Werte von id = 1 und id = 2 enthalten und die Werte der untergeordneten Tabelle die Werte von uid = 2 und uid = 1 enthalten. Wenn wir den Wert von id = 2 in der übergeordneten Tabelle löschen, wird auch der Wert von uid = 2 in der untergeordneten Tabelle direkt gelöscht. Dies ist die Rolle der Kaskade, d. h. der kaskadierenden Löschung.

Schauen wir uns den Fall von „set null“ an:

mysql:yeyztest ::>>Tabelle ändern fk_test_2, Fremdschlüssel fk_uid löschen;   
Abfrage OK, Zeile betroffen (0,02 Sek.)
Datensätze: Duplikate: Warnungen: 

mysql:yeyztest ::>>alter table fk_test_2 add CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`) ON DELETE setze null;
Abfrage OK, Zeile betroffen (0,03 Sek.)
Datensätze: Duplikate: Warnungen: 

mysql:yeyztest ::>>löschen aus fk_test_1, wobei id=;
Abfrage OK, Zeile betroffen (0,00 Sek.)

mysql:yeyztest ::>>Wählen Sie *aus fk_test_1;
Leerer Satz (0,00 Sek.)

mysql:yeyztest ::>>wählen Sie *aus fk_test_2;
+----+------+
| Ich würde | Benutzer-ID |
+----+------+
| | NULL |
+----+------+
 Zeile im Satz (0,00 Sek.)

Es ist ersichtlich, dass nach dem Setzen von „null“ der Wert von „uid“ in der untergeordneten Tabelle null wird, wenn die übergeordnete Tabelle den Wert von „id = 1“ löscht, und der Datensatz nicht gelöscht wird.

Die Situation ohne Aktion ist ähnlich, mit der Ausnahme, dass die Datensätze in der untergeordneten Tabelle nicht geändert werden.

Das Obige ist der Vorgang zum Löschen der übergeordneten Tabelle. Wenn die übergeordnete Tabelle aktualisiert wird, kann die untergeordnete Tabelle auch die oben genannten vier Situationen auswählen, die im Wesentlichen mit dem Löschen identisch sind und hier nicht wiederholt werden. Bei Interesse können Sie es gerne selbst testen.

Abschließend ist zu beachten, dass die Fremdschlüsselspalte der Kindtabelle Nullwerte enthalten kann.

mysql:yeyztest ::>>in fk_test_1 Werte einfügen (,);
Abfrage OK, Zeile betroffen (0,00 Sek.)

mysql:yeyztest ::>>wählen Sie *aus fk_test_2;   
+----+------+
| Ich würde | Benutzer-ID |
+----+------+
| | NULL |
+----+------+
 Zeile im Satz (0,00 Sek.)

mysql:yeyztest ::>>in fk_test_2 Werte einfügen (,NULL);
Abfrage OK, Zeile betroffen (0,00 Sek.)

mysql:yeyztest ::>>in fk_test_2 Werte einfügen (,NULL);
Abfrage OK, Zeile betroffen (0,00 Sek.)

mysql:yeyztest ::>>wählen Sie * aus fk_test_2;
+----+------+
| Ich würde | Benutzer-ID |
+----+------+
| | NULL |
| | NULL |
| | NULL |
+----+------+
 Zeilen im Set (0,00 Sek.)

Oben finden Sie den detaillierten Inhalt der Beispielerklärung zu MySQL-Fremdschlüsseleinschränkungen. Weitere Informationen zu MySQL-Fremdschlüsseleinschränkungen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!