I am designing a mysql database using phpmyadmin 18.104.22.168. Everything in the database is InnoDB.
I have two tables,
person with primary key
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.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
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?