Hey, I was wondering how do I add foreign keys in php MyAdmin? I tried changing the storage type to innoDB, but the option is not available to me ? any ideas ?

Recommended Answers

All 4 Replies

phpMyAdmin lets you define foreign keys using their "relations" view. But since, MySQL only supports foreign constraints on "INNO DB" tables, the first step is to make sure the tables you are using are of that type.

To setup a foreign key so that the PID column in a table named CHILD references the ID column in a table named PARENT, you can do the following:

  • For both tables, go to the operations tab and change their type to "INNO DB"
  • Make sure ID is the primary key (or at least an indexed column) of the PARENT table.
  • In the CHILD table, define an index for the PID column.
  • While viewing the structure tab of the CHILD table, click the "relation view" link just above the "add fields" section.
  • You will be given a table where each row corresponds to an indexed column in your CLIENT table. The first dropdown in each row lets you choose which TABLE->COLUMN the indexed column references. In the row for PID, choose PARENT->ID from the dropdown and click GO.
    By doing an export on the CHILD table, you should see a foreign key constraint has been created for the PID column.

Take video of this:-

Edit:- You have option "operation" in your tab in php admin??
If yes then there you can see table opyions.There you can see storage engine???Change it to "InnoDB"

I tried changing the storage type to innoDB, but the option is not available to me

I've come across web-hosts that have this feature disabled.

Yea i saw this somewhere already but I cant chande my database to InnoDB? theres is only four options. CSV,MRG_MYISAM,memory and MYISAM...

take a look at this.
On running
mysql> SHOW ENGINEs \g you can see what storage engines are there and what it supports.You can clearly see except innoDB no one supports foreign key.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.