Tech Musings

Monday, April 14, 2008

Using Foreign Keys in MySQL

Last week I discovered a neat little feature of MySQL I'd never used before; the ability to use foreign keys as a way to delete (or update) relational records across multiple (i.e. joined) tables. This is called a CASCADE ON DELETE and it has been available as a feature in transactional databases for quite some time. In fact, it has been available in MySQL Server since version 3.23.44. Foreign key constraints are attractive to database designers because they force degrees of referential data integrity.

There were a number things I needed to do to get this to work including changing the engine on my tables from MyISAM to InnoDB, creating indexes and setting up each key relationship. My intent was to implement these changes using phpMyAdmin, but some of the discussions and articles I came across were a bit wanting in this regard and offered little in the way of quick, decipherable information.

Consequently, I decided to create a series of video tutorials demonstrating how I eventually used phpMyAdmin to establish foreign key constraints in my database. Perhaps these little narrated clips will help a fellow "head scratcher" looking to do the same thing with data in his or her MySQL environment. I know they would have saved me time had I come across similar resources as I was doing my own investigation.

On a side note, this is the first time I've used Google video services in this capacity and I'm interested to see how it goes. I decided to use both the built-in video upload feature available in Blogger and Google video (Introduction clip only) to reach a potentially wider audience. I'm not that jazzed about the "watchability" of these videos so I made the higher quality Quicktime movies available for download, too.

Introduction:
The following preface clip introduces the use of foreign keys to CASCADE DELETE related records between parent and child tables.

Download Quicktime version (.mov 2.1 MB)


Step 1:
First, convert all MySQL tables from MyISAM to InnoDB (if needed).
video
Download Quicktime version (.mov 6.3 MB)


Step 2:
Next, designate indexes on appropriate fields.
video
Download Quicktime version (.mov 13.4 MB)



Step 3:
Finally, add foreign key relationships between parent and child tables.
video
Download Quicktime version (.mov 5.8 MB)


Update 5/5/08

MySQL might complain and throw a #1216 or #1452 - Cannot add or update a child row: a foreign key constraint fails SQL error similar to the following when creating foreign key relationships in EXISTING tables which have already been populated with data.

SQL Error: Cannot add or update a child row: a foreign key constraint fails screen shotMySQL error as seen in phpMyAdmin 2.11.5.1 stating there was a foreign key constraint that failed in a child row.

Why? Because orphaned records exist in the child table which relate to a record in the parent table that has already been deleted! Fortunately, I've found some handy SQL queries using LEFT OUTER JOIN here to clean child tables and easily delete unmatched records. As always, don't forget to BACK EVERYTHING UP before attempting these queries!! First, to find "wayward" records with no matching id in a corresponding parent table:
SELECT * FROM `agenda` LEFT OUTER JOIN meetings on
agenda.meeting_id=meetings.meeting_id WHERE
meetings.meeting_id is NULL;
Then, if your query was successful and found the appropriate records:
DELETE agenda.* FROM `agenda` LEFT
OUTER JOIN `meetings` ON agenda.meeting_id =
meetings.meeting_id WHERE meetings.meeting_id IS NULL;

Labels: ,

142 Comments:

  • Jim - thanks for the videos. I was searching around a bit for foreign key & cascade update/delete through phpmyadmin and these really helped me out!

    By Anonymous Anonymous, at 8:22 PM  

  • Thank you so much, I'd never have found the relation view.

    By Anonymous Anonymous, at 7:13 AM  

  • Thanks a lot for your help! This was really useful... I couldn't find this information anywhere else.

    By Anonymous Anonymous, at 3:30 PM  

  • nicely done. thanks for your time and effort

    By Anonymous Anonymous, at 2:46 PM  

  • Likewise with the other comments. VERY Helpful indeed and keep up the great work!!

    By Anonymous Anonymous, at 2:03 AM  

  • Excellent tutorial. Thanks a lot. But please let me askyou : ifi want to create a many to many relationship and I create the extra table for the connection of the tow others, how can I implement the relation?

    The problem isthat when I am creating the table which has only the tow keys their is no "Relation view" to use to create the foreign keys.

    Please help me I would be gratefull.

    By Anonymous Anonymous, at 2:56 PM  

  • thank you

    By Blogger Jason, at 7:24 AM  

  • Great stuff, thanks a million. If you're ever doing some videos on PHP 5 OOP I'd love to know!

    cheers,

    Witt

    By Anonymous Anonymous, at 5:34 AM  

  • thanks

    By Anonymous Anonymous, at 9:47 PM  

  • THANKS A BUNCH, REALLY HELPFUL

    By Anonymous BER, at 7:55 PM  

  • hey, thanx a ton.
    Videos are really cool.
    It helped me so much.


    Sushma.

    By Anonymous Anonymous, at 5:48 PM  

  • Hi Jim, on Dec 14, 2007 you posted your problem with an FTP problem with Dreamweaver. I have the same problem but know nothing about how to solve it. Where can I find this file ' and dump it'?
    ~/Library/Application Support/Dreamweaver 8/Configuration/ssh_hosts

    Thank you in advance

    By Blogger maison yummi, at 7:36 AM  

  • much thanks! Nice way t get a quick answer about phpmyadmin

    By Anonymous Anonymous, at 2:37 PM  

  • Thanks for the tutorial!

    By Anonymous freakmod, at 7:04 PM  

  • Thanks spent 2 hours trying to work out how to do this before stumbling across your post, then all sorted in 10mins :-)

    By Anonymous Kris, at 5:06 AM  

  • Thanks! This was really helpful!

    By Anonymous Maria, at 12:16 AM  

  • Thanks for your videos!!!

    By Blogger Nelson, at 4:28 PM  

  • Thanks. I was looking all over for the view that lets you add foreign keys not realizing that the Relationship link only appears on InnoDB tables.

    By Blogger Trung, at 1:41 PM  

  • Jim, you're a legend, you taking the time to create these videos saved the rest of us hours, if not days of research time. Much gratitude!

    By Anonymous J Gill, at 5:59 PM  

  • Thanks for the video, saved me a lot of time and effort

    By Anonymous Anonymous, at 7:33 AM  

  • Thank you very much.

    I was just startingto write some softare which I think will benefit greatly from foreign keys and this has saved me a lot of work.

    By Anonymous Anonymous, at 3:03 AM  

  • Really nice, 10x from me, too

    By Anonymous Anonymous, at 11:13 PM  

  • Tnx for the tutorial, find it very usefull.

    By Anonymous Anonymous, at 5:23 AM  

  • This looks like it will be exactly what I was after to fill in a couple of blanks.... many thanks for taking the time to put this together for everyone else to use :)

    By Blogger DaFoot, at 1:20 AM  

  • Thank you!

    By Anonymous Bram, at 1:41 AM  

  • Brilliant videos. I will now be using phpMyAdmin for all my foreign key needs!

    By Blogger Disco Patrick, at 5:31 AM  

  • Thank you from Argentina! You save my day... :)

    By Anonymous Anonymous, at 11:36 PM  

  • Thanks! A huge help!

    By Anonymous Anonymous, at 6:06 PM  

  • thanks for the great video tutorials! Would be AWSOME if you could do a tutorial on using the Foreign Keys in action.

    By Anonymous Anonymous, at 9:54 AM  

  • Great tutorial - thanks muchly!

    By Blogger Thomas Haines, at 10:43 PM  

  • Thank you, i had a hard time trying to figure that error

    By Anonymous Anonymous, at 10:56 PM  

  • I agree with all of the above. Thank you very very very much.

    By Anonymous Anonymous, at 7:13 AM  

  • Fantastic, a huge help! Thank you very much.

    By Blogger Adam, at 6:54 PM  

  • Congratulations with your excellent video tutorial!!! You have made technical stuff accessible to me.

    It is much better to see these things in action with your own eyes!!! I am very grateful that you spent time and resources to help. FIVE stars all the way *****

    By Anonymous koyama, at 6:24 PM  

  • This is awesome! Thanks alot!

    By Anonymous Anonymous, at 6:51 PM  

  • Wonderful tutorial. Just what I needed!

    By Anonymous Anonymous, at 12:24 PM  

  • Very helpful! Thanks a lot!

    By Anonymous Anonymous, at 9:34 AM  

  • Thanks alot dude! vids were helpful

    By Anonymous Anonymous, at 10:40 PM  

  • good one man thanks

    By Anonymous james, at 10:03 AM  

  • Thanks a lot. That was very useful!

    By Anonymous Anonymous, at 10:09 AM  

  • i think innodb is not good enough rather than MyISAM database. why? because the innodb is easily may turn to crash.

    By Anonymous Anonymous, at 4:25 PM  

  • Big help!

    By Anonymous Anonymous, at 1:51 PM  

  • As many others i had problems solving the issue but your page helped it a lot. I came across InnoDB mentionings several times but not being an expert on sql or engines i didn't get it. Your videos helped a lot and saved me a lot of time! thanks!

    By Anonymous Anonymous, at 4:34 PM  

  • Brilliant, thanks a lot - this really helped. I too was searching around (and near-guessed what to do) but thankfully my search brought me to your page and excellent videos and seeing them has given me reassurance! :)

    Any idea about composite foreign keys? :)

    By Blogger Gadbury, at 6:57 PM  

  • Thanks !

    By Anonymous Anonymous, at 4:32 PM  

  • Thank you very much, very helpful information.

    By Anonymous Daniel, at 9:53 AM  

  • hey.. thanks a lot for the video's ... excellent stuff... u probably saved my one day !!

    By Anonymous Anonymous, at 2:51 AM  

  • This is great, your a phpMyAdmin rockstar :P

    By Anonymous Ben Waine, at 8:23 AM  

  • Thanks for the tutorial. Very nice.

    By Anonymous Anonymous, at 10:45 AM  

  • Thanks for taking the time to produce this.

    By Anonymous Anonymous, at 7:10 PM  

  • Thank you for these videos! The information helped me figure out the syntax and saved me a lot of frustration.

    By Blogger Kevin, at 1:17 PM  

  • thank you so much. great work

    By Anonymous jax26, at 7:33 PM  

  • Thanks -- I couldn't find any other help on this subject.

    By Anonymous Anonymous, at 10:37 AM  

  • Thank you very much, This tutorial helped me a lot...
    Tnx

    By Anonymous Noam, at 12:31 PM  

  • Great videos! very useful! thanks!

    By Blogger modcube, at 1:45 PM  

  • Wow you are greate man. Good job now a lot of things are clearer. Keep on doing the good job.

    By Anonymous Anonymous, at 11:30 AM  

  • Very helpful! Thank you!

    By Anonymous Jessy, at 10:27 PM  

  • Thanks mate, just what I was looking for :-)

    By Blogger Victor, at 3:41 AM  

  • Thanks, Jim. Really helps a "headscratcher" like me!

    By Anonymous Anonymous, at 7:44 AM  

  • Many thanks-this has been a godsend.

    By Blogger vjwilson, at 6:03 AM  

  • Thanks a lot for the videos! Great stuff really! =)

    By Blogger numero3, at 9:11 AM  

  • Hi Jim, your tutorial was really good thanks!

    By Blogger oddeofun, at 9:48 AM  

  • Thanks for the videos! it help me a lot!!!

    By Blogger nrafalow, at 8:51 AM  

  • Thanks a lot! It save lot of time for me!!

    By Anonymous Anonymous, at 6:21 AM  

  • Thanks a lot! It saved lot of time for me!!

    By Anonymous Anonymous, at 6:22 AM  

  • Very Helpful! Thanks, Jim!

    By Blogger teezecrost, at 2:01 PM  

  • amazing tutorial

    By Anonymous Thanks, at 11:31 PM  

  • Great .. :)

    By Anonymous Anonymous, at 2:35 AM  

  • Many thanks! Very helpful.

    By Blogger Chris Birkett, at 4:23 PM  

  • Thanks Jim, this was clear, concise and easy to follow.

    By Blogger Corporaterel, at 9:49 AM  

  • Thanks a lot.This is really a useful video.

    By Blogger Papiya, at 12:15 AM  

  • Really helpful

    By Anonymous Gallaecio, at 9:20 AM  

  • Brilliant, even in 2010 they are still being used :-)

    By Anonymous Anonymous, at 9:17 PM  

  • Thank you very much for taking the time to post these videos. these were a great help during my database learning adventure :)

    By Blogger Jason, Shaded Pixel LLC, at 1:18 PM  

  • Thank you so very much.This has been very helpful. June

    By Blogger Archie, at 8:39 AM  

  • Thanks Jim. Nice tutorial.

    By Blogger Naveed, at 11:56 AM  

  • Thank you very much!

    By Anonymous Anonymous, at 12:16 AM  

  • Amazing article. I never thought things are so easy like this. MySQL needs foreign key and finally its done.

    By Anonymous Jamil, at 11:08 AM  

  • Thankyou for your effort it has been extremely usefull. Thanks again.

    By Anonymous Anonymous, at 2:01 AM  

  • Thanks very much for the straightforward no nonsense explanation!

    By Blogger Jojo, at 5:33 PM  

  • thanks a ton! extremely helpful :)

    By Blogger swetha, at 2:28 AM  

  • Thanks a lot. this comment from another user and i am asking the same.
    "But please let me ask you : if i want to create a many to many relationship and I create the extra table for the connection of the tow others, how can I implement the relation?

    The problem isthat when I am creating the table which has only the tow keys their is no "Relation view" to use to create the foreign keys."
    Please help me I would be gratefull. it is my graduation project

    By Anonymous Anonymous, at 1:38 AM  

  • thanks a lot , been looking for this for months now! you just made my day buddy

    By Blogger ali, at 7:53 AM  

  • thanks a lot for this , it's been really useful . been looking for this fo months now. you just made my day . thanks again

    By Blogger ali, at 7:55 AM  

  • very useful videos but I was wondering is there a way to creat the corresponding record if it does not exist through the relation ?

    By Blogger Ahmed Hosny, at 8:11 AM  

  • Thank you so very much...

    By Blogger Ron Guilmet, at 11:31 AM  

  • Jim, Just failed a class because I couldn't find this "relationship" information on the mac version and my instructors didn't know the answer.

    Looks like I have to take the class again.

    Thank's so much.

    By Anonymous Anonymous, at 1:49 AM  

  • Thank u my friend, it helped me alot.
    keep on good work.

    By Blogger Elsammak, at 4:41 AM  

  • Hi Jim, Great Job, please keep it up.

    By Anonymous Ahmed Ginani, at 2:12 AM  

  • Lovely Stuff. Very helpful, thank you once again.

    By Anonymous Dan, at 12:19 AM  

  • Thanks a Lot DUDE.... :) It Save me Lot of Time keep it up.... thanks once again

    By Blogger balamahendran, at 5:30 AM  

  • Thank A lot DUDE ... Really it save me lots of time.... :)

    By Blogger balamahendran, at 5:31 AM  

  • Thanks, very usefull, that was just what i need :D

    By Anonymous Mirza Delic, at 3:51 PM  

  • vow....really very very helpful... thanks a lot...

    By Blogger ASHKAR SIDHEEQUE, at 3:04 AM  

  • vow,really very useful. it helped me a lot.thanks....

    By Blogger ASHKAR SIDHEEQUE, at 3:06 AM  

  • Many thanks for the tutorial, I fumbled through some other ones, but this one is the best I've seen. It's true a picture does say a thousand words. Thanks agaon

    By Blogger Joe, at 12:20 PM  

  • thank you for doing this , you said at the start of the vid that reason you created this video was for someone like me to save time.

    Im used to other database software (oracle) and trying to muddle through php myadmin for a uni project and not being able to figure out fkeys

    thanks again

    By Anonymous synthaside, at 12:09 PM  

  • somit: thanks buddy :).I always use to do with writing code, not with phpmyadmin.

    By Anonymous Anonymous, at 11:34 PM  

  • You saved my day! Thanks for the videos!

    By Anonymous Anonymous, at 6:21 AM  

  • Great tutorial !!! Thanks for this nice work :)

    By Blogger esunilkumare, at 5:36 AM  

  • Muchas gracias!!, estabamos trabados con foreing key. Los videos muy buenos.
    Saludos desde Argentina

    By Anonymous Anonymous, at 2:08 PM  

  • Thank you very much for great tutorial. Finally this was my end of searching for InnoDB.

    By Anonymous Anonymous, at 8:06 AM  

  • Great tutorial! it is so helpful

    By Blogger Programming Documentation Blog, at 4:19 AM  

  • Jim. Thank you, Thank you and Thank you. I was searching for quite some time before I came across your video. you just saved me so much of frustration.

    By Anonymous Anonymous, at 4:25 AM  

  • Thanks alot!!!!!!!! Really this a great tutorial...once again thanks

    By Anonymous Krishna Mohan, at 3:06 AM  

  • Thank you! Excellent tutorial! Now i could do that using PHPMyAdmin

    By Blogger Thiago Falc√£o, at 11:08 AM  

  • Oh Man, that's very cool. Thanks from Mitch

    By Anonymous Anonymous, at 4:31 PM  

  • That LEFT OUTER JOIN is horrible for finding missing records. Use NOT EXISTS instead.

    SELECT *
    FROM `agenda`
    WHERE NOT EXISTS (
    SELECT * FROM `meetings` WHERE `meeting_id` = `agenda`.`meeting_id`
    )

    Then to DELETE:

    DELETE
    FROM `agenda`
    WHERE NOT EXISTS (
    SELECT * FROM `meetings` WHERE `meeting_id` = `agenda`.`meeting_id`
    )

    This is much easier to write and clearer to understand.

    By Anonymous cake_and_bacon, at 7:39 AM  

  • Hey thanks a lot for the videos!
    They were realllly helpfull in my project.

    By Blogger Vish, at 10:34 PM  

  • Much thanks to you. Awesome job on the videos even with the low res. I had no problem picking right up on what you were showing me.:-)

    By Blogger Ian, at 8:14 PM  

  • Thanks for making this tutorial, I forgot that you need innoDB to use foreign keys :S

    By Anonymous Anonymous, at 4:13 AM  

  • This was a tremendous help to me. I too could not even find the relation view but once you identified the conversion to the innoDB it all came together. You saved me a lot of time as there is not another comprehensive guide online. This should be included on the phpMyAdmin site!

    By Anonymous Anonymous, at 1:12 PM  

  • Thank you so much for this! My database professor has never used MySQL or phpmyadmin before, but the department told him he had to teach using MySQL, so he is pretty much leaving us to figure out things on our own. These videos were extremely helpful. I'm going to tell other people in my class about them.

    By Anonymous LostInSpace, at 5:30 PM  

  • Thanks man, that was so what I was looking for :)

    By Anonymous Anonymous, at 5:34 AM  

  • thanks a lot man!

    By Anonymous Anonymous, at 2:45 AM  

  • Thank you very much Jim - your videos helped me a lot.

    By Anonymous Anonymous, at 4:33 AM  

  • Thank you really for this!!!
    I've been looking for a way to create relationships in phpmyadmin for so long. I've even come across a method where the config files needed to be changed. As much as possible, I want to avoid doing this, because then my database won't be compatible with my classmates' not-yet-configured phpmyadmin (we have a group project, so each of us needed a copy of the database).
    Finding this method really helped me out. Thanks!!! ^_^

    By Blogger M. K. Alegre, at 5:22 AM  

  • Thanks for this!!! ^_^
    I've been searching for a way to be able to enforce relationships while using phpmyadmin to set up my tables. I've even come across a method where I needed to edit some config file. As much as possible, I wanna avoid that method because then I could not properly import the database to my classmates' pc (we're wrking on a group project).
    This tutorial post really helped me out, thanks! ^_^

    By Blogger M. K. Alegre, at 5:26 AM  

  • Thanks alot for such a great explanation!

    By Anonymous Adrian, at 5:13 AM  

  • Thanks you for videos. It was very helpfull for me. Very good job and article.

    thx Tom (CZECH REP.)

    By Anonymous Anonymous, at 2:14 AM  

  • Hey, thanks. Saved me whole load of time..

    By Anonymous Anonymous, at 12:04 PM  

  • Thank you for your great job. You have saved me at one practice what i have to do.

    ^_^

    By Blogger Ignizar, at 6:11 AM  

  • thanks very much.

    By Anonymous Anonymous, at 4:16 AM  

  • thanks very much.

    By Anonymous Anonymous, at 4:17 AM  

  • Thanks lot.very useful.

    By Anonymous Anonymous, at 11:51 AM  

  • Hi Jim, thanks for the videos. Very helpful to me as I'm starting out in relational databasing.

    By Anonymous Anonymous, at 1:38 PM  

  • Thanks!

    By Blogger Damian, at 12:21 PM  

  • grt!!!!thanks a lot.

    By Anonymous Anonymous, at 3:05 AM  

  • Thank you very much!

    By Anonymous Anonymous, at 11:33 AM  

  • thank you very much!

    By Anonymous Anonymous, at 11:33 AM  

  • Awesome! Thanks, this tutorial was really helpful.

    By Anonymous Anonymous, at 9:11 PM  

  • great videos really helped would have taken me alot longer to sort this without these videos

    By Anonymous Anonymous, at 6:01 AM  

  • Thanks for putting those videos together! Very helpful.

    By Blogger Adam, at 8:57 AM  

  • Great tutorial mate!!Thanks a lot !

    By Anonymous Anonymous, at 2:57 AM  

  • thanks a lot:)

    By Anonymous Anonymous, at 10:22 PM  

  • Thanks a lot:)

    By Anonymous Anonymous, at 10:24 PM  

  • Thanks for you quick tutorial, It really help us !

    By Anonymous Anonymous, at 12:12 AM  

  • Thanks very much. Really helpful. :)

    By Anonymous Anonymous, at 8:06 AM  

  • Thank you!!!!!
    I spent much time trying to add FK in phpMyAdmin.

    By Blogger MetalRolera, at 8:37 PM  

  • Thank you !!!!It has been very helpful to me. :)

    By Blogger MetalRolera, at 8:42 PM  

  • It has been very helpful to me.
    Thank you!

    By Blogger MetalRolera, at 8:42 PM  

  • Super cool!!! Thanks alottttt..!!!!
    U saved my time! :)

    By Anonymous Meena, at 11:37 PM  

Post a Comment

Links to this post:

Create a Link

<< Home