MySQL Foreign Key – Can a Column Be Referenced from Multiple Tables?

You will get errors if you restore a row that contains a value in nQuestionId2 that references a value in nQuestionId1 that has not been created yet.

There are a few ways to resolve this issue:

• You could ensure that you create the data in the correct order.
• You could disable the foreign keys before importing the data, then re-enable them after.
• If disable/re-enable is not an option, you could drop/recreate them.
• Alternatively you could split out the data so that the nQuestionId1 is in a separate table - this will allow you to restore all the nQuestionId1 values first.

I guess but the question really is about what you are trying to achieve.

I have seen designs like this that have a ID column and then a PARENT column that references the ID. In order to get the root parent you have to recurse through all of the parents of the object you are looking at. This means that even though a design like this may make sense logically, querying it could become quite difficult.

Database design is not just about mapping business rules and objects to tables and constraints, but also thinking about what is maintainable, how will the design hold up against large amounts of data, and being pragmatic about your approach.

I hope this helps you.

The answer is yes, they are lacking foreign keys, the previous dba was lacking all referential integrity and was using an application stage enforcement of the dba logic (as discussed here: http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html). The previous dba must have been inexperienced and possibly used this method:

For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table

However as me joining the project has proven - it is not necessarily a good way of doing it.

Given the sensitive nature of this project and the later desire to have all kinds of distributed systems accessing the db it has been decided that the overhead of having foreign key constraints is worth it for security when others may be programming and accessing the db.

Whilst my comment on my question shows that it is possible for mysqldump to strip out important stuff and leave one with a misunderstanding, in this case there was no indexing and no foreign key relationships (someone else showed me, I will include how they found the answer if I can catch and get them to tell me).

From what I've been reading and seeing, Drupal does not really commonly use foreign keys, but that's another story altogether.