WeberDev.com PHP and MySQL Code

LOG IN
BEGINNER GUIDESPHP CLASSESCODE SEARCHARTICLES SEARCHPHP FORUMSPHP MANUALPHP FUNCTIONS LISTWEB SITE TEMPLATES
Start typing to search for PHP and MySQL Code Snippets and Articles Search


Submit a code Example / Snippet Join us on FaceBook
Submit a code Example / Snippet Submit Your Code
Search Engine Optimization Monitor SEO Monitor
Web Site UpTime Monitor UpTime Monitor
Your Personal Examples List My Favorite Examples
Your Personal Articles List My Favorite Articles
Edit Account Info Update Your Profile
PHP Code Search
Web Development Forums
Learn MySQL Playing Trivia
PHPBB2 Templates
Web Development Resources
Web Development Content
PHPClasses
PHP Editor
PHP Jobs
Vision.To Design
Ajax Tutorials
PHP Programming Help
PHP/MySQL Programming
Webmaster Resources
Webmaster Forum
XML meta language
website builder



Go Back Add a Comment Send this Article to a friend Add this Article to your personal favoritest for easy future access to your favorite Code Examples and Articles. Submit a code example Print this code example.
BACK ADD A COMMENT SEND TO A FRIEND ADD TO MY FAVORITES SUBMIT AN ARTICLE PRINT
Title : Managing Foreign Key Relationships In MySQL Using SQLyog
Categories : Databases, MySQL, SQLyog Picture not available
Insanely Great
Date : 2003-04-03
Grade : 3 of 5 (graded 3 times)
Viewed : 88747
Search : More Articles by Insanely Great
Action : Grade This Article
Tools : My Favotite Articles


Submit your own code examples 
 


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

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.

Getting Started

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.


Conclusion

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!









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
Start Using MySQL
Categories : MySQL, Databases, To MySQL, Beginner Guides
Alternating row colors with PHP and mySQL
Categories : PHP, Databases, MySQL, HTML and PHP