The same foreign key in multiple tables

Question

I saw posts on SO and through google, stating that with Mysql you cannot have multiple foreign keys with the same name. My problem is how to refer to one column from one table in several other tables. In my case, I have a FAMILY table containing FAM_ID. I want it to be a foreign key in my DOCUMENTS and CONTACT tables, because the rows in them are related to FAM_ID. Therefore, if I correctly understand what I read, I need to specify a column in DOCUMENTS and CONTACT of different names (than FAM_ID) so that they display them as a foreign key for FAM_ID in the FAMILY table. I just don’t understand something or is this my only option? This seems to be a fairly common data model requirement.

Answer 1

You do not need to call them different things. You can have a FAM_ID column in FAMILY, a FAM_ID column in DOCUMENTS, and a FAM_ID column in a contact.

In this case, calling them one and the same, it becomes clear that they refer to the same thing. But you can also come up with a different naming convention if you want.

For example, I use something along the lines family_ID in the family table and contact_familyID , document_familyID when accessing it from contacts and documents tables.

The only drawback to calling them the same, as I see it, is that when you perform joins, you cannot just refer to them by the column name, you must pseudonize them or refer to them by the name tablename.columnname.

Edit: I think I found what you mean without having the same name. This is so when you add a foreign key relationship within the same table, as described here: http://bugs.mysql.com/bug.php?id=46363

Answer 2

fk_family_documents

fk_family_contact

Setting foreign key names to something like the above will be one way to do this, I'm not sure if it's an alias, but yes. Here is an example syntax if you add it as a constraint:

ALTER TABLE documents ADD CONSTRAINT fk_family_documents_id FOREIGN KEY (fam_id) REFERENCES family(id)

Of course, you may have to adapt this, but it should give you an idea of ​​how it will be configured.

Answer 3

I have the same problem, I have not noticed any good solutions. I originally wanted to keep the names of my foreign key the same as the primary key they referenced in order to make natural joins possible. Therefore, I will rename my foreign keys to something like {reference_primary_key} _1, {reference_primary_key} _2, etc.