Menu

#3102 (ok 4.3) Relation view on a table omits showing existing foreign keys

4.0.4
fixed
1
2014-12-05
2010-09-03
No

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.

Discussion

  • Anonymous

    Anonymous - 2010-09-03

    DB DDL file (no data needed)

     
  • Anonymous

    Anonymous - 2010-09-03
    • priority: 5 --> 7
     
  • Marc Delisle

    Marc Delisle - 2010-10-11
    • assigned_to: nobody --> lem9
     
  • Marc Delisle

    Marc Delisle - 2010-10-11

    This is a known limitation, see FAQ 3.6 in Documentation.html.

     
  • Marc Delisle

    Marc Delisle - 2010-10-11
    • status: open --> pending-later
     
  • SourceForge Robot

    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).

     
  • SourceForge Robot

    • status: pending-later --> closed-later
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: closed-later --> postponed
     
  • Marc Delisle

    Marc Delisle - 2013-07-27
    • status: postponed --> open
    • assigned_to: Marc Delisle --> nobody
    • Group: 3.3.6 --> 4.0.4
     
  • Hugues Peccatte

    Hugues Peccatte - 2013-11-16

    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.

     
  • Hugues Peccatte

    Hugues Peccatte - 2013-11-16

    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.

     
  • Marc Delisle

    Marc Delisle - 2014-03-14
    • assigned_to: Marc Delisle
     
  • Marc Delisle

    Marc Delisle - 2014-11-28
    • summary: Relation view on a table omits showing existing foreign keys --> (ok 4.3) Relation view on a table omits showing existing foreign keys
    • status: open --> resolved
    • Priority: 7 --> 1
     
  • Marc Delisle

    Marc Delisle - 2014-12-05
    • Status: resolved --> fixed
     
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.