InnoDB Foreign Keys

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

• 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

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

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;