Monday, May 25, 2009

////

How to Add Foreign Key in MySQL Query Browser

-- Begin --
*Prerequisite: knowledge in MYSQL and MYSQL GUI tools especially MYSQL Query Browser (just so you could relate ^^,)

So, have you ever tried using MySQL GUI for structuring your database? If yes, then good for you since MYSQL Query Browser, Administrator, Migration Toolkit, etc. will make your database construction (and destruction) a lot easier than doing it with the console (MYSQL Command Line Client). However, it seems that manipulating foreign keys are harder using the MYSQL Query Browser. Personally, I've experienced a lot of crappy errors while trying to add a foreign key using the Query Browser. (#@*%$&*@#$*&$ crappy s#!t mySQL, are these bugs of the software? wtf?omgggeee!!!) [relax] ... ^^,

I don't know if those were bugs, I haven't asked the developers themselves (i don't have time for that, I need solutions!)

Solution: Follow these steps in adding the foreign key of your choice ^^,
Click the images for a larger view!

1. Open the table where you want to add a foreign key
(if you don't know what I mean then you're lost forever here). I'll use or database table 'department' to demonstrate the process. (Aww, I'm so kind.. haha)

2.
Add a field that will represent the foreign key in your table. For example, you will add a 'collegekey' field in your 'department' table. (This means that a department belongs to a college). Now, be careful of the data types. Make sure that the data type of the field that you want to be a foreign key is the same as the field that you will refer to.

3. Afterward, select the tabbed pane that says "Foreign Keys" in MYSQL Table Editor.
4. Click the plus/add [ + ] button below.

5. You should be seeing the Add Foreign Key dialog now that asks for the foreign key name. Never mind putting the name as it will trigger error in the process. ( this is not official, I just assumed it based on my experience)

6. In the "Foreign Key Settings Pane", select the "Ref. Table:" drop down list. Select which table would the foreign key refer to. In our case, I chose the "college" table since the college key is there. This is my reference table.
7. Then select the values of the Column and Reference Column into their right content. Be sure that the foreign key in this table ACTUALLY refers to the field in the referred column. You have the option to edit the CONSTRAINTS. (On Delete/ On Update: Restrict/ Cascade/ No Action/ Set to Null)
8. Apply Changes. If error occurs, don't panic, it's just normal. Don't try again by doing the same things you've done in applying the Foreign key constraint because same error will appear, believe me. Maybe you just missed out something here. Try to find the error ^^,


** haaay.. effort.. :D

-- End --

9 Reactions to this post

Add Comment
  1. jet MUSIC said... July 7, 2009 at 1:58 AM

    please notify me if all your efforts of following these instructions fail so I may able to double check everything.. for questions and other reactions, feel free to comment here or in the shout box :D

  2. swapna said... November 11, 2009 at 11:11 AM

    In my case, i don't see any errors after I click on Apply. But I don't see any entries in Foreign Key tab. Am I missing anything?

  3. jet MUSIC said... November 13, 2009 at 7:55 PM

    @swapna: usually after you click the 'Apply Changes' button, you will not see the entries... Try to check the table again and click the 'Foreign Keys' tab. If the entry is not there that means you failed to put the foreign key.. :)

  4. pan2 said... April 16, 2011 at 3:08 AM

    still says error 1005 : /

  5. kathnel said... August 15, 2011 at 7:11 PM

    thanks it works for me!

  6. Stack said... September 12, 2011 at 11:21 PM

    we should not be able to enter any value ,to the foreign key ,that is not present in the parent key. am i right?? but by this process i can enter values those are not present in parent table. let me know if i am wrong

  7. Anonymous said... May 16, 2012 at 3:14 AM

    Thanks .....

  8. Anonymous said... December 13, 2012 at 12:02 AM

    ayus. advance reading lang naman. :)

  9. Anonymous said... March 9, 2016 at 2:51 AM

    I am having error no 1005.