Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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