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> | ||
