InnoDB Foreign Keys

Features

  • Forces database consistency
  • No need to rely on application for referential integrity

Usage

  • Primary use is for parent-child table relationships
  • Parent-child relationships can exist within the same table
  • Column types for both indexes must be exactly the same
  • Character set must also be the same for non-binary columns
  • InnoDB rejects any creation of records where the constraints do not match
  • You can use ALTER TABLE to retroactively apply a foreign key match, but the indexes must be valid
  • Use SHOW CREATE TABLE to display foreign key indexes

Notes

  • MyISAM tables will accept the syntax, but not actually do anything

Examples

Parent-child relationship in two tables:

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;