Foreign Key

Used to create a Parent/Child relationship between two tables.

 

Provides "Referential Integrity" (by using the same field in both tables), that ensures consistency of data across both tables (i.e. changes to one are reflected in the other).

 

The (child) Foreign Key links to the (parent) primary key of the related table, using the same field:

 

 

 

In the example above, the parent "Customers" table has a primary key called "CustID", and the child "Orders" table has a primary key called "OrderID". A relationship is established between the two tables by adding a foreign key in the child Orders table that links to the same "CustID" field in the parent Customers table.

 

This is achieved using the following command to create the child Orders table:

 

create table Orders ( OrderID int primary key not null auto_increment, CustID int not null, Part varchar(20), Cost int, FOREIGN KEY(CustID) REFERENCES Customers(CustID) ) ;

 

Accordingly, the child Orders table, has a CustID field that is set as its FOREIGN KEY that REFERENCES the reciprocating CustID field in the parent Customers table.

 

To delete data in Child automatically from Parent, add "on delete cascade" just before closing bracket:

 

create table Orders ( OrderID int primary key not null auto_increment, CustID int not null, Part varchar(20), Cost int, FOREIGN KEY(CustID) REFERENCES Customers(CustID) ON DELETE CASCADE) ;

  • only in innodb
  • not MyISAM

Leave a Reply