How does one add a #FOREIGN KEY in a table while using xampp phpmyadmin

    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:

    1. For both tables, go to the operations tab and change their type to "INNO DB"
    2. Make sure ID is the primary key (or at least an indexed column) of the PARENT table.
    3. In the CHILD table, define an index for the PID column.
    4. While viewing the structure tab of the CHILD table, click the "relation view" link just above the "add fields" section.
    5. 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.

      robin_percy wrote:

      1. For both tables, go to the operations tab and change their type to "INNO DB"

      I have gone to the operations tab I get a list of options in table type but I do not see the INNO DB option do i have to type it manually somewhere. I see table type and the options are MyISAM, MEMORY, ARCHIVE, MRG_MYISAM.
      It seems I am missing a step somewhere or i am in the wrong place
      thanks
      paddy

        Hmm. What version of XAMPP are you running? And what version of phpMyAdmin and MySQL does it show when you first open phpMyAdmin?

          I am running xampp ver 1.5.0-pl
          and phpmyadmin 2.6.4-pl3
          MySQL 5.0.15
          thanks

            2 years later

            I have posted the details of setting foreign keys using phpmyadmin at [link removed - why an external link when you can just post it here?]:

            The following steps are required for you to be able to add foreign keys using phpadmin (in xampp).

            1. Select any table and go to operations tab, if InnoDB is listed under storage engine drop down menu then jump to step 5.

            2. So InnoDB is disabled in mysql engine, edit the \xampp\mysql\bi\my.cnf file and remove the hashes that are required to enable InnoDB (its self explanatory).

            3. Add a line in the above file, default_storage_engine=InnoDB (This makes InnoDB the default storage engine).

            4. Restart mysql from the xampp control panel or from MS services.

            5. Make sure, both PARENT and CHILD tables are of storage type InnoDB.

            6. REFERENCED key (in parent) should be primary and REFERENCING key (in child) should be index.

            7. In the CHILD table’s structure view, click on the link ‘relations view’ (it lies above ‘add fields’).

            8. In the row corresponding to the REFERENCING key, select the REFERENCED key from the second column drop box.

            9. Click go .. you’ll see that the required query is executed.

              For storage type information go here.

              Write a Reply...