With the introduction of InnoDB table handlers in standard MySQL distribution, MySQL has entered a new phase. InnoDB provides MySQL with a transaction-safe table handler with commit, rollback, and crash recovery capabilities. InnoDB tables support FOREIGN KEY constraints as the first table type in MySQL. They are included in the MySQL source distribution starting from 3.23.34a and are activated in the MySQL -Max binary. For Windows the -Max binaries are contained in the standard distribution.
In this article we will discuss how to manage Foreign Key Relationships with MySQL in an easy way with SQLyog, a Windows based free GUI Front End to MySQL. You can download the latest copy of SQLyog at http://www.webyog.com/sqlyog/download.html
What is Foreign Key?
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
Table – titles
title_id int PRIMARY
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.
More about Foreign Key
Although the primary purpose of a FK constraint is to control the data that can be stored in the FK table, it also controls changes to data in the primary key table. For example, if the row for a publisher is deleted from the FK table, and the publisher's ID is used for books in the titles 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 primary key table if those changes invalidate the link to data in the FK table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail if the deleted or changed primary key 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 FK table or change the FK data in the FK table, thereby linking the FK to different primary key data.
Foreign Key In InnoDB
Both tables involved in the relationship have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the first columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly.
Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types have to be the same. The length of string types need not be the same.
Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. Corresponding ON UPDATE options are available starting from 4.0.8. If ON DELETE CASCADE is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. If ON DELETE SET NULL is specified, the child rows are automatically updated so that the columns in the foreign key are set to the SQL NULL value.
Foreign Keys In SQLyog
SQLyog treats one of the tables in the relationship as the child and the other parent. In the above example the TITLES table is the child and the PUBLISHERS table is the parent.
Using SQLyog you create relationships on the child table that will refer to the columns of the parent table.
First of all you need to create indexes on the columns of both TITLES and PUBLISHERS i.e. pub_id. You can do this by using SQLyog’s Index Manager that you can start by selecting the table in the tree window on the left hand side (i.e. the Object Browser) and selecting Manage Indexes… from the popup menu.
(screenshot of the Index Manager )
After the indexes has been created properly, select TITLES in the Object Browser and choose Manage Relationships… from the popup menu. You will get a dialog like Figure A.
Note: If the table is not of InnoDB type, SQLyog will give an error.. You can change the handler of table by selecting it in the Object Browser and changing it to appropriate one through Change Table Type To… option from the popup menu. Check back if it has changed to InnoDB otherwise InnoDB is not setup properly.
Since we don’t have any relationships created yet, the Reference Grid is empty. We will now create a relationship between titles.pub_id and publishers.pub_id. To create a new relationship, press New…
SQLyog will start up the Create Relationship dialog as shown in Figure B.
Select publishers table in the Reference Table combo box. The small command buttons in Key and Reference Table dialog box starts the Index Manager for the respective tables. This is very helpful is you forget to create the right indexes before starting this dialog.
After you have selected publishers in the combo box, select pub_id in both Source Column and Target Column of the Grid. A sample is shown in Figure C.
If you want to create a FK on multiple columns just select those columns one by one in the Grid and after you are done press Create.
Note: If you want to set rules for On Delete or On Update operations then you have to do it now.
If the FK is created successfully, SQLyog will close the dialog box and will go back to Manage Relationship dialog. The Manage Relationship dialog will look like Figure D.
Deleting A Relationship
As of now, MySQL does not provide you with a one-query-method to delete a FK (it is in the TO-DO list). MySQL and InnoDB docs suggest dropping the table and recreating the table without the relationship. If you have tables with lots of columns and data, this process really becomes tedious.
However, SQLyog let you overcome this pain, and does all the steps internally. You are just required to select Delete!. Internally, SQLyog does this using a 11-step process.. If you want to delete a FK just select the relationship from the Grid and press Delete.
Since, this process can result in loss of data SQLyog will ask for confirmation (Figure E). Press Yes at your own risk.
After the process is completed successfully, the relationship will be removed from the Grid to show the updated state of the table.
When I started using InnoDB, it looked quiet overwhelming. I almost used to faint when I needed to change the FK parameters. Thankfully, SQLyog cam just in time!
|Saving Images in MySQL|
Categories : MySQL, PHP, Graphics, Databases
Categories : PHP, MySQL, HTTP, Databases
|Access vs. MySQL|
Categories : Databases, MySQL, MS Access
|Beginners guide to PHP and MySQL|
Categories : PHP, Beginner Guides, Databases, MySQL, Installation
|Descriptions of Common Data Types|
Categories : MySQL, Databases, PHP, PHP options/info, General
|Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML|
Categories : PHP, MySQL, Databases, DHTML
|Unicode and Other Funny Characters|
Categories : Databases, MySQL, Unicode
|Building A Persistent Shopping Cart With PHP and MySQL|
Categories : PHP, MySQL, Databases, Ecommerce
|Case Study: Handling MySQL Growth With a PHP Class|
Categories : Databases, MySQL, PHP
|MySQL Access Control System - Grant Tables|
Categories : Databases, MySQL, Security
|PHP, MySQL and Authentication 101|
Categories : PHP, Databases, MySQL, Authentication
|How Logs Work On MySQL With InnoDB Tables|
Categories : Databases, MySQL, InnoDB
|Practical Date and Time examples with PHP and MySQL|
Categories : Databases, MySQL, PHP, Date/time
|Creating Users and Setting Permissions in MySQL|
Categories : Databases, MySQL
|Speaking SQL part 2|
Categories : General SQL, Databases, MySQL