Mysql – Phptheadmin / thesql error defining primary keys as foreign keys of two separate tables

foreign keyMySQLprimary-key

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?

Best Answer

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

Related Solutions

Composite primary key from multiple tables / multiple foreign keys

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Mysql – create foreign keys for pivot tables

Foreign key are constraints for data consistency, and they should be added always as a security measure. In order for the foreign keys to be fast, MySQL also creates its corresponding indexes unless the columns are already indexed. That is independently of the performance question: create the foreign keys to avoid storing incorrect data.

In most cases, you may want to index the pivot table because the most typical queries that you will be doing are along these lines:

SELECT * 
FROM Reservation RV
JOIN pivot p ON p.reservation_id = RV.id
JOIN Resource RS ON p.resource_id = RS.id
WHERE filter(column_on_Reservation_or_Resource)

In that case, you will traverse the pivot table in Reservation->Resource direction or viceversa. For that, the best index would be pivot(reservation_id, resource_id) (and/or viceversa if you are using it in the other direction), as that way you can get advantage of the covering index optimization. One of the directions can be the primary key, if the pairs happen to be unique (relationships cannot be multiple among the same pair of records from reservation and resource).

Best Answer

Related Solutions

Composite primary key from multiple tables / multiple foreign keys

Mysql – create foreign keys for pivot tables

Related Question