Foreign key in phpmyadmin

Hi I am creating a website and linking it to a database using php and mysql. I need to create the databases in phpmyadmin and have decided on the structure of the tables in the database. However, as expected, there are foreign keys in some of the tables - does anyone know how to link the foreign key of one table into another table within phpmyadmin so the database works properly and can be queried using sql? I would really appreciate help with this. Thanks

It is very easy if you have the following:

  • Latest phpMyAdmin installed.
  • The storage engine of both tables are InnoDB. Not MyISAM.
  • You have primary keys on both tables.
  • You have an index on both fields that are going to connect. If the field in the first table is the primary key, you don’t need to index it again.

If you have all that, you go to structure view in the second table. Under the table with all the field information there is a link “Relation view”. Click that and make the connection.

Thank you Dennis. I did what you have told but still I am not able to get foreign key. Do I need to make ny change in php script?

No, this thing is internal to the database.

Do you see the “Relation view” link under the structure table?

Yes I used the relation view under the structure table. However I dont get it why it is not working.

To save you time in phpmyadmin you can ALWAYS use the tab marked SQL and create any table creation statement, inserts, updates etc. from there. So if you are having trouble using the GUI you can set your foreign keys in the SQL tab.

SQL is always an alternative. But if the requirements are not met, the SQL call will also fail.
Back to the problem, if you can get into the relation view, that means that your table is indeed in InnoDB format.
Do you see the field that needs to reference to the primary table? If not, you are probably at the wrong table.
Does the row with the field have form controls? If not, then you probably haven’t created the appropriate indexes (primary and secondary).
If you see them enabled, then you just use them to make the connection.

you can say this about anything

if you don’t click the right icons in phpmyadmin, you will fail

if you don’t submit the right foreign key SQL, you will fail

if you don’t know how foreign keys are supposed to work, you will probably fail no matter how you go about it

the nice thing about SQL is that it will always work, and you’ll never have to learn some unfriendly gui which actually just generates SQL under the covers

My initial thought was to get the problem solved. The second, in reference to r937, is that what if you only use point/click with a GUI and then somehow move to a set-up where there is no GUI or perhaps one you are not familiar with. now what do you do if you don’t know how to ALSO code by hand.

The average MySQL user does not have that deep knowledge guys. And MySQL is a success because of those people. So lets not be too strict. It is good to know MySQL in depth, but if you have a simple project and you can do your job in simpler means, I don’t see why not use them instead of raw SQL.

i agree completely

so these simpler means, by which you mean the icons and buttons in phpmyadmin, why is it so hard to get them to work?

Well… this is completely off topic. So the OP can ignore these posts.

<Philosophical discussion>

Because there is the business logic that is common for a user of a GUI and an SQL coder. From that point on, the SQL coder must know the syntax which adds a lot to the learning curve.

For example I know the computer language TCL. It is unique because it has about 5 commands. Is it reasonable to demand everyone to learn it? And even if you spend 1-2 hours to learn the basic commands, would you be willing to spend endless hours in exceptions? Like for example in adding or removing items in associative arrays.

The people who have a deep understanding of a system usually forget how it looks from the outside. The last years there is this new area in computing called UX, who’s mission is to fight the kind of thinking of people like us (coders) :slight_smile:

</Philosophical discussion>

not if you’re a frequent poster, like i am

believe me, i have seen and heard it all when it comes to people learning SQL

i completely understand how tough it is

and yet i still believe that their understanding is not improved by using a gui – often, it’s the opposite