0

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?

  • Use commandline mysql client - it will give you better error messages. – jkavalik Feb 1 '16 at 19:24
  • I don't have access to the mysql command line directly, but if necessary I can get the guy who runs the server to help me with this. It didn't occur to me that the error message might be different there. Thanks! – MasterOfNone Feb 2 '16 at 4:09
0

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

  • Thanks. It really does just say "Error" without a number or reason. I'm grateful for you testing it so that I know I'm right that it should work. As far as the ID, I had it unique and auto-incrementing before. I tried making it the primary and making person_id_number and e-mail_id_number a single unique as you suggested, but when I tried to make them both foreign keys I got the same answer. I changed them back to PK and dropped id_number, and still I can't make the two foreign keys to different tables. I am really confused. – MasterOfNone Feb 2 '16 at 4:04
  • @MasterOfNone now you know it should work properly, it probably is a PHPMyAdmin related bug or something. – Edu C. Feb 2 '16 at 14:46
  • In the same database, with the same collation, engine, datatypes, and so forth, I created two new tables and mapped them together as originally attempted with person and e-mail_address. It worked. I went back and tried with person and e-mail_address and it still doesn't work. This seems like an application glitch. I'm just dropping and recreating the tables in question. You're marked as answer because you confirmed that the error should not occur. – MasterOfNone Feb 3 '16 at 1:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.