Differences
This shows you the differences between two versions of the page.
innodb_foreign_keys [2012/06/28 10:08] |
innodb_foreign_keys [2012/06/28 10:08] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== 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: | ||
+ | |||
+ | <code> | ||
+ | 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; | ||
+ | </code> |