I have seen posts on SO and thru google proclaiming that with Mysql you can't have multiple foreign secrets of the identical title. My problem is how do you reference exactly the same column in one table in multiple other tables. During my situation I've got a FAMILY table that consists of FAM_ID. I would like this to become a foreign type in my DOCUMENTS and phone tables because rows in individuals link with FAM_ID. Therefore if I realize what I have read properly I have to title the column in DOCUMENTS and phone different names (than FAM_ID) to be able to ask them to map like a foreign answer to FAM_ID in the household table. Shall We Be Held simply not understanding something or perhaps is this my only option? It appears like this is a reasonably common dependence on an information model.
Setting the foreign key names to something similar to the above mentioned could be just one way of doing the work, I don't know if that's aliased, but yea. Here's a good example syntax if you're adding it as being a constraint:
ALTER TABLE documents ADD CONSTRAINT fk_family_documents_id FOREIGN KEY (fam_id) REFERENCES family(id)
Granted, you might want to tailor that, but should provide you with a concept of how it might be setup.
You don't have to title them various things. You'll have a FAM_ID column in FAMILY, a FAM_ID column in DOCUMENTS along with a FAM_ID column in touch.
Within this situation naming them exactly the same factor causes it to be obvious they make reference to exactly the same factor. But, you might develop another naming convention of your if you want.
For instance, I personally use something like
family_ID in the household table, and
document_familyID when mentioning into it in the contact and document tables.
The only real disadvantage to naming them exactly the same, so far as I can tell, is the fact that when you are performing joins you cannot just make reference to them by column title, you need to alias them or make reference to them by tablename.columnname.
Edit: I believe I have found that which you mean by the inability to have a similar title. This is actually the situation whenever you give a foreign key relationship inside a single table, as referred to here: http://bugs.mysql.com/bug.php?id=46363
I am getting this same problem, havn't really observed worthwhile solutions. I initially desired to keep my foreign key names just like the main key they recommended, to create natural joins possible. So I'm going to be renaming my foreign secrets to something similar to _1, _2, etc..