I cannot set Foreign key in PHPmyadmin XAMPP

Question

When i need to set a field as the foreign key in a table,in PHPMyAdmin. I am not getting it set right.

There is a Parent table called 'user' which has a primary key called 'uid'.I am using the 'uid' in the child table called 'student_register' as foreign key. But while setting it as foreign key constraint through the relation view link in phpmyadmin . i am not able to see the 'user' table in the drop down list to select it and set the 'uid' as foreign key .. I have sent the screen shot to get a clear picture. the screenshot for the phpmyadmin child table 'student_register'

Answer 1

If you are facing this issue than you need to follow these basic steps:

1. First login into the phpMyAdmin

2. Then open the database

3. Then open the table where you want to apply the Foreign Key

4. Then go to the Operation Tab

5. Then go to the Table Options

6. Then check the storage engine in the table options

7. If the storage engine type is MyISAM then you can not apply the Foregin Key in that database because the storage engine MyISAM does not support foreign key constraints

8. That why you need to change the Storage Engine Type: InnoDB from the MyISAM

9. Then click on Go Button for apply the Changes

Answer 2

From the MYSQL User Guide:

If you are facing this issue than you need to follow these basic steps:

1. Database storage engine must be InnoDB.
2. Your relational tables must be InnoDB.
3. Use UNIQUE reference key.
4. Reference Key can not be NULL.
5. Datatype of the both columns must be same.

References:

Create Table Foreign Keys in MYSQL

Creating Foreign Key Relationships

Answer 3

You should add index to the column uid in table user in order to appear in the drop down list

Take the structure of table user, then click the last option of the Action - Add index for the column uid.