Same foreign key in multiple tables

Question

I've seen posts on SO and through google stating that with Mysql you cannot have multiple foreign keys of the same name. My issue is how do I reference the same column from one table in multiple other tables. In my case I have a FAMILY table that contains FAM_ID. I want this to be a foreign key in my DOCUMENTS and CONTACT tables because rows in those have a relationship with FAM_ID. So if I understand what I've read correctly I need to name the column in DOCUMENTS and CONTACT different names (than FAM_ID) in order to have them map as a foreign key to FAM_ID in the FAMILY table. Am I just not understanding something or is this my only option? It seems like this would be a fairly common requirement of a data model.

Answer 1

fk_family_documents

fk_family_contact

Setting the foreign key names to something like the above would be one way of doing it, I am not sure if that is aliased, but yea. Here is an example syntax if you are adding it as a constraint:

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

Granted, you may have to tailor that, but should give you an idea of how it would be setup.

Answer 2

You don't need to name them different things. You can have a FAM_ID column in FAMILY, a FAM_ID column in DOCUMENTS and a FAM_ID column in CONTACT.

In this case naming them the same thing makes it clear that they refer to the same thing. But, you could also come up with another naming convention of your own if you like.

For example, I use something along the lines of family_ID in the family table, and contact_familyID, document_familyID when referring to it from the contact and document tables.

The only downside to naming them the same, as far as I can see, is that when doing joins you can't just refer to them by column name, you have to alias them or refer to them by tablename.columnname.

Edit: I think I've found what you mean by not being able to have the same name. This is the case when you add a foreign key relationship within a single table, as described here: http://bugs.mysql.com/bug.php?id=46363