[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to set up a foreign key using  phpMyAdmin

Posted on 2007-10-09
13
Medium Priority
?
17,302 Views
Last Modified: 2012-05-05
Hi ALL;

How to set up a foreign key to a table in MySQL using  phpMyAdmin ( 2.9.2 )?

Please help
0
Comment
Question by:tantan6611
  • 7
  • 6
13 Comments
 
LVL 28

Expert Comment

by:gamebits
ID: 20041424
1) You need a table set with InnoDB (MyISAM does not support foreign key constraint)
2) Click on the Structure tab.
3) Under the table shown there is a link "Relation View"
4) That's where you set up the constraint.
0
 

Author Comment

by:tantan6611
ID: 20042055

Very nice  But what should I asgin the colmun that I intend to put as a foreign key for another table .....a Primary key,  index or unique key ?!
0
 

Author Comment

by:tantan6611
ID: 20045257
gamebits...
Please explain in more details step 4 what should I do exactly...

4) That's where you set up the constraint.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 28

Expert Comment

by:gamebits
ID: 20045362
Create an index on the field you want to use for foreign key constraint.

Then when you click on Relation view you will have 3 text boxes with the field(s) you want to use.

Lets suppose you have a table name Customer with a field name invoice_id, you also have a table named Invoice with the primary field named invoice_id.

If you open Relation view on Customer you will see a list of all the fields in that table at the top yu will see
Links to

invoice_id [drop down with the list of all the table fields you can link to] ON DELETE [drop down with the options Cascade
SET NULL
NO ACTION
RESTRICT]
ON UPDATE [drop down with the same options]

So you could setup that the invoice will be deleted if you delete the customer link to it [cascade]

I don't know how complex is your application but this is the basic on how to set the foreign key with phpmyadmin
0
 

Author Comment

by:tantan6611
ID: 20047056
Ok great this is what I'm doing ...
BUT after creating an index for  invoice_id field in  Customer table and then choosing
 invoice_id [Invoice->invoice_id] ON DELETE [Cascade]ON UPDATE [Cascade]  
And then press Save
I got this error messege in red at the top of the page
No index defined! ( invoice_id)

So what I did wrong?!!

0
 
LVL 28

Accepted Solution

by:
gamebits earned 2000 total points
ID: 20048222
Do you have define an index on invoice_id in Invoice table as well?

Do you have some data in the tables?

And lastly make sure you have the same data type in both tables, invoice_id set as VARCHAR in one table and set as INT in the other will bring a message error.
0
 

Author Comment

by:tantan6611
ID: 20064029
Your right, the problem is that the tables are populated
So this means that I can not create a foreign key  in populated tables?
0
 
LVL 28

Expert Comment

by:gamebits
ID: 20064297
You can create a foreign key in populated tables but the fields would have to be of the same type, change the type if needed.

Thanks for the points and the grade.
0
 

Author Comment

by:tantan6611
ID: 20064733

 The types are the same (varchar) in both fields!
0
 
LVL 28

Expert Comment

by:gamebits
ID: 20064745
What error message do you get?
0
 

Author Comment

by:tantan6611
ID: 20064812

 it givs me the same error messege as before "No index defined! ( invoice_id)"!



0
 
LVL 28

Expert Comment

by:gamebits
ID: 20064834
Did you indexed invoice_id in both tables?
0
 

Author Comment

by:tantan6611
ID: 20064965
yes...
 invoice_id  is a (primary key) in Invoice table and (index) in Customer table
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 4 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question