InnoDB unterstützt auch Fremdschlüssel-Constraints, die in InnoDB mit folgender Syntax definiert werden:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_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:

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:

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 TABLE tbl_name
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_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 TABLE tbl_name DROP FOREIGN KEY fk_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 FROM db_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 dump_file_name;
mysql> 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.