Create a new database, here called "geoinfo". Take the attached DDL script. It will create 6 tables without data. (should be type InnoDB)
Now click on the DB, click the 'cities' table. The "Structure" tab should show two indexes correctly: 'PRIMARY' on all columns, 'cities_states_fk' on country_code and state_code (FK to 'states'). Now click "Relation view" somewhere in the middle. This doesn't show any foreign keys at all!
To confirm, click the SQL tab and issue an 'SHOW CREATE TABLE cities'. This will show one row with the 'Create Table' cell showing the correct SQL:
CREATE TABLE `cities` (
`country_code` char(2) NOT NULL,
`state_code` varchar(5) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`country_code`,`state_code`,`name`),
KEY `cities_states_fk` (`country_code`,`state_code`),
CONSTRAINT `cities_states_fk` FOREIGN KEY (`country_code`, `state_code`) REFERENCES `states` (`country_code`, `code`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
As you can see, the foreign key exists, but phpMyAdmin doesn't show it in the relation view. THIS CAN BE EXTREMELY CONFUSING! I thought something with my DDL or the server was wrong. Please fix this ASAP.
This is for v3.3.6, but v3.2.3 also exposed this behavior.
DB DDL file (no data needed)
This is a known limitation, see FAQ 3.6 in Documentation.html.
This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 14 days (the time period specified by
the administrator of this Tracker).
Hi,
I have an error while importing this SQL file.
I believe this is because of my MySQL version: 5.5.27. I believe that in this version you can't name an index as a FK.
Here is another version of the file with different names for indexes. So you can import it.
The "Relations" screen is good to (un)define FK with unique field.
In this case, this is a FK with 2 fields, so I think this is normal not to display this FK currently. How could we show this FK in this screen please? Does anyone have any idea?
Thanks.
There is a feature request about this:
https://sourceforge.net/p/phpmyadmin/feature-requests/919/
and it's scheduled to be a GSoC 2014 task, see
http://wiki.phpmyadmin.net/pma/GSoC_2014_Ideas_List#Interface_improvements