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_number
and 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?
mysql
client - it will give you better error messages.