Foreign Key

Among other features some Table Engines support FOREIGN KEY constraints. An InnoDB tables was the first table type available in MySQL supporting Foreign Keys. They were introduced in the MySQL 3.23 tree. With MySQL version 5.1 MySQL AB introduced what they call the 'pluggable Storage Engine architecture' so that (third party or experimental) Engines can be added without recoding and compiling the MySQL server itself. InnoDB still is the only table TYPE (or 'ENGINE' as is the correct term form MySQL version 4.1) supporting Foreign Key Constraints that MySQL AB ship themselves, but the third party Engines 'PrimeBase XT' or just PBXT and SolidDB are now available with Foreign Key support. Also the Falcon storage engine by MySQL AB will have FK's functional enabled at a later point of its development. Currently SQLyog supports Foreign Keys with both InnoDB and Primbase XT and SolidDB.

Foreign Key constraints are (almost) functionally identical to 'relationships' in some databases like Microsoft Access.

Depending on the MySQL version and the platform where it is running, InnoDB may or may not be available, and PrimeBase XT will only be available where it has been installed as a 'plugin' (addition) to the server as shipped by MySQL AB. InnoDB is included in every recent binary distributed by MySQL AB. However some ISP's disable it (completely or for some (the cheapest!) 'hosting plans' only). If the system variable 'have_innoDB' has the value of "YES" it is available. You can check this from the SQLyog menu ... tools .. show .. variables. For PBXT and SolidDB the system variable is 'have_PBXT' and 'have_soliddb''

Before attempting to create Foreign Keys, you should ensure that all the tables involved are either InnoDB, PBXT or SoldiDB tables - and the same type. You cannot have a InnoDB 'parent' and a PBXT 'child' in a FK relationship.

If the tables are different types you can use the 'Change tabletype ..' from the table menu or context menu.

What is FK?

A foreign (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table.

You can create a foreign key by defining a foreign key constraint when you create a table.

A Sample Database

To understand the concept in a better way we create two tables with the following structure -

Table - publishers

pub_id int PRIMARY
pub_name char
city char
state char
country char

Table - titles

title_id int PRIMARY
title char
type enum
pub_id int
price double
advance double
royalty int
ytd_sales bigint
notes blob
pubdate date

In the above example, the titles table has a link to the publishers table because there is a logical relationship between books and publishers. The pub_id column in the titles table matches the primary key column of the publishers table. The pub_id column in the titles table is the FK to the publishers table. Publishers table is 'parent' and Titles table is 'child' or 'referencing' table.

Although the primary purpose of a FK constraint is to control the data that can be stored in the 'child' table, it also controls changes to data in the 'parent' table. For example, if the row for a publisher is deleted from the Publishers/'parent' table, and the publisher's ID is used for books in the Titles/'child' table, the relational integrity between the two tables is broken; the deleted publisher's books are orphaned in the titles table without a link to the data in the publishers table. A FK constraint prevents this situation. The constraint enforces referential integrity by ensuring that changes cannot be made to data in the 'parent' table if those changes invalidate the link to data in the 'child' table. If an attempt is made to delete the row in a 'parent key' or to change a referenced value in the parent key, the action will fail if the deleted or changed referenced value corresponds to a value in the FK constraint of another table. To change or delete a row in a FK constraint successfully, you must first either delete the FK data in the child table and/or change the FK data in the 'parent' table, thereby linking the FK to different 'parent'/referenced data.

It is common (and practical in most situations) to have a setup as the above example: the referenced value in the 'parent' table is also the Primary Key for that table. But more setups are possible.

For more information see The SQLyog Schema Designer.