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.
If you already have the tables somewhere (perhaps in a temporary, non-relational setup), you can use a left join to find “orphaned” rows. This will find records in $t1 that refer to non-existant rows in $t2. Just fill in the variables.
SELECT t1.$key
FROM $t1 AS t1
LEFT JOIN $t2 AS t2
ON t1.$foreign_key = t2.$key
WHERE t2.$key IS NULL
Add A Comment