Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Phpmyadmin version 4: Relation view sometimes does not show foreign key constraints

I have a database that I built a while back. Every table in the database is InnoDb. Several tables had foreign key constraints, and I set them up for On Delete = Cascade. When I was using an earlier version of phpmyadmin, working with these was simple: I'd just go to the Structure tab of a table, click the Relation View link, and as long as I had the correct indexes set up on the correct columns, I could set the foreign keys as I saw fit.

Since upgrading to version 4, it's become a nightmare. For some tables, I go to the relation view and everything is just fine. But for others--even when they already have foreign key constraints set--I can't see any options for working with them.

To make matters worse, I've even tried dropping the indexes and re-adding them, only to be given the following error: Cannot drop index [index_name]: needed in a foreign key constraint. So unless I'm mistaken, the constraint is there, but phpmyadmin is refusing to show it to me.

Is there something I have to do to make them show up again? This is extremely frustrating to say the least: something that worked just fine before now does not thanks to an upgrade.

like image 828
blainarmstrong Avatar asked Sep 02 '14 05:09

blainarmstrong


People also ask

How do I display foreign keys in phpMyAdmin?

To see FKs of a table first select table from the object explorer, then go to Structure tab and then select Relation view. Please note that in different versions it might be in different locations. On the Relation view screen you will see all foreign keys defined for this table (as a foreign table).

How do I fix foreign key constraint failure?

The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.

Can views have foreign keys?

In the strict sense of the word, no you cannot set foreign keys on views.

How do you identify foreign key constraints?

Open the Table Designer for the table containing the foreign key you want to view, right-click in the Table Designer, and choose Relationships from the shortcut menu. In the Foreign Key Relationships dialog box, select the relationship with properties you want to view.


2 Answers

OK, after playing around with the tables a bit, I figured out what's going on. The only time the foreign key constraint options don't show up are when the table names contain capital letters. Very frustrating to say the least.

like image 152
blainarmstrong Avatar answered Sep 28 '22 04:09

blainarmstrong


I just filed a bug report for phpmyadmin: https://github.com/phpmyadmin/phpmyadmin/issues/11461

It should be an easy fix.

like image 45
Basti Avatar answered Sep 28 '22 03:09

Basti