Foreign Keys

A foreign key relationship allows for the declaration of an index in one table to be related to an index in another.  It also allows for the placing of constraints on what may be done to the tables in the relationship.  The database enforces the rules of this relationship to maintain referential integrity. Using Foreign Keys with InnoDB The InnoDB storage engine supports the use of foreign keys (unlike other storage engines).  This capability enables the declaration of relationships between columns in different tables, and InnoDB maintains integrity between the tables by prohibiting operations that violate those relationships.  For example, the following restrictions must be addressed:  

•  A table must contain only records with ID values that are known in another reference table.  

•  If an ID in the referenced table is changed, the ID in all matching records in the referencing table must be changed to match.  

•  If a record with a given ID in the referenced table is deleted, all records with the matching ID in the referencing table must also be deleted.  The following example demonstrates some of the ways in which InnoDB provides referential integrity between tables.  It shows how to define a foreign key relationship that enforces the requirements just described.  The example is based on the implicit relationship between the Country and City tables in the world database: 

•  Each Country record has a Code column that specifies a unique country code.  

•  Each City record has a CountryCode column that matches the code for the country in which the city is located.  The relationship is only implicit because Country and City are MyISAM tables: MyISAM does not have any syntax for specifying the relationship explicitly and provides no means for enforcing it. This means that Code value could be changed in the Country table and any City records with the corresponding country code would not be changed to match.  A Country table row could be deleted but the corresponding City records would not be deleted.  In either case, the City records would become orphaned because there is no longer any corresponding Country table record for them.  InnoDB does not allow these types of referential 

integrity failures to occur.  



To define a country-city relationship explicitly, derive a couple of  InnoDB tables from the MyISAM tables. We'll call these tables country_parent and city_child to illustrate that the country records are the parent records and the city records are the child records that depend on them:  


CREATE TABLE country_parent 
( 
    code CHAR(3) NOT NULL, 
    name CHAR(52) NOT NULL, 
    PRIMARY KEY (code) 
) ENGINE = InnoDB; 

CREATE TABLE city_child 
( 
    id           INT NOT NULL AUTO_INCREMENT, 
    name         CHAR(35) NOT NULL, 
   country_code CHAR(3) NOT NULL, 
    PRIMARY KEY (ID), 
    INDEX (country_code), 
    FOREIGN KEY (country_code) 
        REFERENCES country_parent (code) 
        ON UPDATE CASCADE 
       ON DELETE CASCADE 
) ENGINE = InnoDB; 




In these two tables, the column and PRIMARY KEY definitions are the same as in the original Country and City tables. The parts of the syntax that differ from the original tables are the ENGINE table option, which specifies the InnoDB storage engine, and the  INDEX and  FOREIGN KEY definitions for the CountryCode column in the city_child table.