Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tables

Question

I am designing a mysql database using phpmyadmin 3.3.10.4. Everything in the database is InnoDB.

I have two tables, person with primary key person.id_numberand e-mail_address with pk e-mail_address.id_number. Since one person can have many e-mail addresses and since sometimes two people share a single e-mail address, I consider this a many to many relationship. I am trying to create a mapping table to relate them, map_person_e-mail_address. I have the table already created with PK on two columns, map_person_e-mail_address.person_id_number and map_person_e-mail_address.e-mail_address_id_number. I also have an auto-incrementing, unique map_person_e-mail_address.id_number. I need this because later a table for tracking the history of e-mails sent will have a foreign key on this ID number to indicate who was e-mailed and what e-mail address was used.

All of this has worked so far. However, in the relation page for my mapping table, I am trying to set map_person_e-mail_address.person_id_number as the a foreign key on person.id_number and map_person_e-mail_address.e-mail_address_id_number as a foreign key on e-mail_address.id_number. I can set either foreign key individually, but when I try to make both columns foreign keys I receive a message that just says "Error" without any explanation.

Am I not able to set the two columns in the PK as FKs from two different tables? If not, can someone please let me know why and what I should do instead? Or if that's not the problem, then what is?

Answer 1

Tried to create a database with the exact tables, PKs and FKs as yours and I receive no error. Tried both creating the tables with the constraints and without it, creating the relationships via PHPMyAdmin.

This "Error" you're getting really doesn't have a message or a error number?

To answer you, yes, you can set two columns as Primary Keys that are also Foreign Keys. They should look like this:

person_id_number 1 (referencing p.id_number 1)
email_addres_idnumber 1 (referencing e.id_number 1)

Now, as they are PKs, you can't repeat them. You could only use the Person ID 1 to another Email ID, and vice-versa.

But, as you also have and ID number in your map, there will be some repetitions:

id_number 1
person_id_number 1
email_address_id_number 1

Looks ok, right? But:

id_number 1
person_id_number 1
email_address_id_number 2

Now you have two id_numbers, because your Primary Key consists of three different tables. What I'd suggest you is for you to create the PK as the id_number of the map table and the foreign keys as a Unique Key. It should look like PK(id_number), UK(person_id_number, email_address_id_number).