InnoDB
unterstützt auch
Fremdschlüssel-Constraints, die in InnoDB
mit folgender Syntax definiert werden:
[CONSTRAINTsymbol
] FOREIGN KEY [id
] (index_col_name
, ...) REFERENCEStbl_name
(index_col_name
, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
Für Fremdschlüsseldefinitionen gelten folgende Bedingungen:
Beide Tabellen müssen InnoDB
-Tabellen
sein und dürfen keine TEMPORARY
-Tabellen
sein.
In der referenzierenden Tabelle muss ein Index bestehen, in dem die Fremdschlüsselspalten als erste Spalten in derselben Reihenfolge aufgeführt sind. Ein solcher Index wird automatisch auf der referenzierenden Tabelle angelegt, wenn er noch nicht existiert.
In der referenzierten Tabelle muss ein Index bestehen, in dem die referenzierten Spalten als erste Spalten in derselben Reihenfolge aufgeführt sind.
Index-Präfixe auf Fremdschlüsselspalten werden nicht
unterstützt. Dies hat unter anderem zur Folge, dass
BLOB
- und TEXT
-Spalten
nicht in einen Fremdschlüssel eingebunden werden können,
da Indizes auf diesen Spalten immer ein Längenpräfix haben
müssen.
Wenn die CONSTRAINT
-Klausel
verwendet wird, muss der
symbol
symbol
-Wert in der ganzen
Datenbank einzigartig sein. Ist die Klausel nicht angegeben,
erstellt InnoDB
den Namen automatisch.
InnoDB
weist jede INSERT
-
oder UPDATE
-Operation zurück, die versucht,
einen Fremdschlüsselwert in einer Kindtabelle anzulegen, wenn
kein passender Schlüsselwert in der Elterntabelle vorhanden
ist. Was InnoDB
mit einer
INSERT
- oder
UPDATE
-Operation anfängt, die versucht, in
der Elterntabelle einen Schlüsselwert zu ändern oder zu
löschen, zu dem in der Kindtabelle passende Zeilen vorhanden
sind, hängt davon ab, welche
Referenzaktion in den Teilklauseln
ON UPDATE
und ON DELETE
der FOREIGN KEY
-Klausel angegeben ist. Wenn
der Benutzer versucht, in der Elterntabelle eine Zeile zu
ändern oder zu löschen, zu der in der Kindtabelle eine oder
mehr passende Zeilen vorhanden sind, bietet
InnoDB
fünf mögliche Optionen:
CASCADE
: Bei Löschung/Änderung einer
Zeile der Elterntabelle werden automatisch die zugehörigen
Zeilen der Kindtabelle auch gelöscht oder geändert. Es
gibt sowohl ON DELETE CASCADE
als auch
ON UPDATE CASCADE
. Zwischen zwei Tabellen
sollten Sie bitte nicht mehrere ON UPDATE
CASCADE
-Klauseln definieren, die auf derselben
Spalte der Eltern- oder Kindtabelle arbeiten.
SET NULL
: Bei Löschung/Änderung einer
Zeile der Elterntabelle werden automatisch die zugehörigen
Fremdschlüsselspalten der Kindtabelle auf
NULL
gesetzt. Das gilt nur, wenn die
Fremdschlüsselspalten nicht als NOT NULL
definiert sind. Sowohl ON DELETE SET NULL
als auch ON UPDATE SET NULL
wird
unterstützt.
NO ACTION
: Im Standard-SQL bedeutet
NO ACTION
tatsächlich keine
Aktion in dem Sinne, dass jeder Versuch, einen
Primärschlüssel zu löschen oder zu ändern, unterbunden
wird, wenn es dazu einen Fremschlüsselwert in der
referenzierten Tabelle gibt. InnoDB
weist
dann die Lösch- oder Änderungsoperation auf der
Elterntabelle zurück.
RESTRICT
weist die Lösch- oder
Änderungsoperation auf der Elterntabelle zurück.
NO ACTION
und RESTRICT
sind dasselbe wie ein Auslassen der ON
DELETE
- oder ON UPDATE
-Klausel.
(Manche Datenbanksysteme kennen verzögerte Prüfungen
(deferred checks), zu denen auch NO
ACTION
gehört. Da in MySQL
Fremdschlüssel-Constraints jedoch sofort geprüft werden,
sind NO ACTION
und
RESTRICT
hier dasselbe.)
SET DEFAULT
: Diese Aktion wird zwar vom
Parser anerkannt, aber InnoDB
weist
Tabellendefinitionen mit ON DELETE SET
DEFAULT
- oder ON UPDATE SET
DEFAULT
-Klauseln zurück.
Beachten Sie, dass InnoDB
Fremdschlüsselreferenzen in einer Tabelle unterstützt. In
solchen Fällen sind „Datensätze der Kindtabelle“
in Wirklichkeit abhängige Datensätze in derselben Tabelle.
Da InnoDB
Indizes auf Fremdschlüsseln und
referenzierten Schlüsseln verlangt, können
Fremdschlüsselprüfungen schnell durchgeführt werden und
erfordern keinen Tabellen-Scan. Der Index auf den
Fremdschlüsseln wird automatisch angelegt. Das war in manchen
älteren Versionen anders, wo Indizes explizit angelegt werden
mussten, da sonst keine Fremdschlüssel-Constraints angelegt
werden konnten.
Zusammengehörige Spalten im Fremdschlüssel und referenzierten
Schlüssel müssen in InnoDB
ähnliche
Datentypen haben, damit sie sich ohne Typkonvertierung
vergleichen lassen. Die Größe und das Vorzeichen von
Integer-Typen müssen gleich sein. Die Länge von
String-Typen muss nicht unbedingt identisch sein. Wenn Sie
SET NULL
verlangen, dürfen die
Spalten der Kindtabelle nicht als NOT NULL
deklariert sein.
Wenn MySQL die Fehlernummer 1005 aus einer CREATE
TABLE
-Anweisung meldet und die Fehlermeldung sich auf
Fehlernummer 150 bezieht, schlug die Tabellenerstellung fehl,
weil ein Fremdschlüssel-Constraint nicht wohlgeformt war. Wenn
ein ALTER TABLE
scheitert und auf
Fehlernummer 150 verweist, bedeutet dies, dass eine
Fremdschlüsseldefinition für die geänderte Tabelle nicht
korrekt war. Die Anweisung SHOW ENGINE INNODB
STATUS
zeigt eine detaillierte Erklärung des letzten
InnoDB
-Fremdschlüsselfehlers im Server an.
Hinweis:
InnoDB
prüft Fremdschlüssel-Constraints
nicht auf Fremdschlüsseln oder referenzierten Schlüsseln, die
eine NULL
-Spalte enthalten.
Hinweis: Trigger werden von kaskadierenden Fremdschlüsselaktionen derzeit nicht aktiviert.
Abweichung von SQL-Standards:
Wenn mehrere Zeilen in der Elterntabelle denselben
Referenzschlüsselwert haben verhält sich
InnoDB
bei Fremdschlüsselprüfungen so, als
würden die anderen Zeilen der Elterntabelle, also die mit
demselben Schlüsselwert, gar nicht vorhanden. Wenn Sie
beispielsweise einen RESTRICT
-Typ-Constraint
definiert haben und es eine Kindzeile mit mehreren Elternzeilen
gibt, verbietet InnoDB
das Löschen
irgendeiner dieser Elternzeilen.
InnoDB
führt kaskadierende Operationen mit
einem Depth-First-Algorithmus durch, beruhend auf Einträgen in
den Indizes, die zu den Fremdschlüssel-Constraints gehören.
Abweichung von SQL-Standards:
Ein FOREIGN KEY
-Constraint, der einen
Nicht-UNIQUE
-Schlüssel referenziert, ist
nicht Standard-SQL, sondern eine
InnoDB
-Erweiterung dieses Standards.
Abweichung von SQL-Standards:
Wenn ON UPDATE CASCADE
oder ON
UPDATE SET NULL
rekursiv dieselbe
Tabelle ändert, die zuvor während der
kaskadierenden Änderung auch bereits aktualisiert wurde,
verhält es sich wie RESTRICT
. Sie können
also keine rückbezüglichen ON UPDATE
CASCADE
- oder ON UPDATE SET
NULL
-Operationen ausführen. Dadurch sollen
Endlosschleifen wegen kaskadierender Updates verhindert werden.
Andererseits ist ein rückbezügliches ON DELETE SET
NULL
jedoch möglich, nämlich als rückbezügliches
ON DELETE CASCADE
. Die maximale
Schachtelungstiefe von kaskadierenden Operationen beträgt 15
Ebenen.
Abweichung von SQL-Standards:
Wie immer in MySQL prüft InnoDB
für jede
SQL-Anweisung, die viele Zeilen einfügt, löscht oder ändert,
die UNIQUE
- und FOREIGN
KEY
-Constraints zeilenweise. Nach dem SQL-Standard
sollte eigentlich eine verzögerte Prüfung vorgenommen werden,
bei der Constraints erst nach der Verarbeitung der
gesamten SQL-Anweisung geprüft werden. Bis die
verzögerte Constraint-Prüfung auch in
InnoDB
implementiert ist, werden einige Dinge
nicht möglich sein, etwa das Löschen eines Datensatzes, der
sich über einen Fremdschlüssel auf sich selbst bezieht.
In dem folgenden einfachen Beispiel sind
parent
- und child
-Tabellen
über einen Einzelspalten-Fremdschlüssel verbunden:
CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;
Das folgende, komplexere Beispiel zeigt eine
product_order
-Tabelle, die Fremdschlüssel
für zwei andere Tabellen besitzt. Ein Fremdschlüssel
referenziert einen Zwei-Spalten-Index in der
product
-Tabelle und der andere einen
Ein-Spalten-Index in der customer
-Tabelle:
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB; CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=INNODB;
InnoDB
ermöglicht es, mit ALTER
TABLE
einer Tabelle einen Fremdschlüssel-Constraint
hinzuzufügen:
ALTER TABLEtbl_name
ADD [CONSTRAINTsymbol
] FOREIGN KEY [id
] (index_col_name
, ...) REFERENCEStbl_name
(index_col_name
, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
Bitte legen Sie immer zuerst die
erforderlichen Indizes an. Sie können einer Tabelle
mit ALTER TABLE
auch einen rückbezüglichen
Fremdschlüssel-Constraint hinzufügen.
InnoDB
ermöglicht es überdies, mit
ALTER TABLE
Fremdschlüssel zu löschen:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_symbol
;
Wenn die FOREIGN KEY
-Klausel bei der
Erzeugung des Fremdschlüssels einen
CONSTRAINT
-Namen enthielt, können Sie beim
Löschen dieses Fremdschlüssels denselben Namen nennen.
Ansonsten wird beim Anlegen des Fremdschlüssels intern ein
fk_symbol
-Wert generiert. Um zum
Löschen eines Fremdschlüssels diesen Symbolwert zu ermitteln,
dient die SHOW CREATE TABLE
-Anweisung. Ein
Beispiel:
mysql>SHOW CREATE TABLE ibtest11c\G
*************************** 1. row *************************** Table: ibtest11c Create Table: CREATE TABLE `ibtest11c` ( `A` int(11) NOT NULL auto_increment, `D` int(11) NOT NULL default '0', `B` varchar(200) NOT NULL default '', `C` varchar(175) default NULL, PRIMARY KEY (`A`,`D`,`B`), KEY `B` (`B`,`C`), KEY `C` (`C`), CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11a` (`A`, `D`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`) REFERENCES `ibtest11a` (`B`, `C`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB CHARSET=latin1 1 row in set (0.01 sec) mysql>ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;
Sie können einen Fremdschlüssel nicht in separaten Klauseln
derselben ALTER TABLE
-Anweisung anlegen und
löschen. Hierzu sind zwei getrennte Anweisungen erforderlich.
Der Parser von InnoDB
gestattet es, für
Tabellen- und Spaltenbezeichner in einer FOREIGN KEY
… REFERENCES …
-Klausel Backticks als
Anführungszeichen zu verwenden. (Alternativ können doppelte
Anführungszeichen gesetzt werden, wenn der SQL-Modus
ANSI_QUOTES
SQL eingeschaltet ist.) Außerdem
berücksichtigt der InnoDB
-Parser die
Einstellung der Systemvariablen
lower_case_table_names
.
InnoDB
gibt die Fremdschlüsseldefinitionen
einer Tabelle im Rahmen der SHOW CREATE
TABLE
-Anweisung zurück:
SHOW CREATE TABLE tbl_name
;
mysqldump erstellt ebenfalls die korrekten Definitionen der Tabellen in der Dump-Datei und vergisst dabei auch die Fremdschlüssel nicht.
Die Fremdschlüssel-Constraints einer Tabelle können wie folgt angezeigt werden:
SHOW TABLE STATUS FROMdb_name
LIKE 'tbl_name
';
Die Fremdschlüssel-Constraints stehen in der
Comment
-Spalte der Ausgabe.
Bei den Fremdschlüsselprüfungen errichtet
InnoDB
Shared-Sperren auf Zeilenebene auf den
relevanten Datensätzen der Kind- oder Elterntabellen.
InnoDB
prüft die Fremdschlüssel-Constraints
sofort und nicht erst beim Committen der Transaktion.
Damit Dump-Dateien, die Fremdschlüsselbeziehungen haben,
leichter geladen werden können bindet
mysqldump automatisch in die Dump-Ausgabe
eine Anweisung ein, die FOREIGN_KEY_CHECKS
auf 0 setzt. So entstehen keine Probleme mit Tabellen, die beim
Neuladen der Dump-Dateien eigentlich in einer bestimmten
Reihenfolge geladen werden müssen. Diese Variable kann auch
manuell gesetzt werden:
mysql>SET FOREIGN_KEY_CHECKS = 0;
mysql>SOURCE
mysql>dump_file_name
;SET FOREIGN_KEY_CHECKS = 1;
So können Sie die Tabellen in beliebiger Reihenfolge
importieren, wenn die Dump-Datei Tabellen enthält, die
eigentlich gemäß ihrer Fremdschlüssel nicht richtig geordnet
sind. Außerdem wird der Import beschleunigt. Auch in Fällen,
wo Sie Fremdschlüssel-Constraints in LOAD
DATA
- und ALTER TABLE
-Operationen
ignorieren möchten, kann es sinnvoll sein,
FOREIGN_KEY_CHECKS
auf 0 zu setzen.
Mit InnoDB
können Sie eine durch einen
FOREIGN KEY
-Constraint referenzierte Tabelle
nur löschen, wenn SET FOREIGN_KEY_CHECKS=0
eingestellt wurde. Wenn Sie eine Tabelle löschen, werden die in
ihrer Erzeugungsanweisung definierten Constraints mit gelöscht.
Wenn Sie eine gelöschte Tabelle wiederherstellen, muss ihre Definition zu den auf sie bezogenen Fremdschlüssel-Constraints passen. Sie muss die richtigen Spaltennamen und -typen sowie Indizes auf den referenzierten Schlüsseln haben. Ist dies nicht der Fall, gibt MySQL die Fehlernummer 1005 zurück und nennt die Fehlernummer 150 in der Fehlermeldung.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.