Composite Foreign Key Constraint via PhpMyAdmin?

Question

Is there a way to add a composite (multi-column) foreign key to an InnoDB table via the PhpMyAdmin interface? I already have the appropriate composite primary key in the target table, and I can single column foreign key constraints, but I can't find a way in the interface to do the composite one.

Table Log
- Date
- Service
PRIMARY KEY (Date,Service)

Table Issue
- Issue_Id
- Log_Date
- Log_Service
PRIMARY KEY Issue_Id
FOREIGN KEY (Log_Date=Log.Date,Log_Service=Log.Service)

Everything is already set up except the foreign key; I go into the PhpMyAdmin relation view and I can only see how to set up a FK against Log.Date and not against Log.Service at all.

Chrisse

Did you ever find the answer? I can only figure out how to set up simple FK constraints in phpMA. – Guntar

Answer 1

I just ran into a similiar problem setting up a composite foreign key in phpMyAdmin and maybe my solution could help you as well.

Here's my setup:

As you can see I have a composite primary key in my ConferenceRoom table, part of which is also a foreign key to streetaddress in table Office (this might differ from your problem).

This composite primary key is to be referenced by a composite foreign key in table InstPicture but the problem for me was that, in spite of having defined both as primary key indexes, phpMyAdmin would only reference the name column in the ConferenceRoom primary key, and not the office_streetaddress column.

What I was missing here was that I had not set up the relation between ConferenceRoom and Office before trying to set up the one between ConferenceRoom and InstPicture. I guess I forgot to handle the strong enities first, as database methodology dictates.

When the relation had been defined between ConferenceRoom and Office, the office_streetaddress column showed up in the list of indexed columns and could the be referenced by the conferenceroom_office_streetaddress column in table InstPicture.

I hope this could help you as well, maybe try creating a separate index for your Log.Service column. Or if that is a FK, set up its relations and then try again.

Hocker