MySQL: A foreign key referencing two parents

Question

Have an interesting situation: I have a foreign key from a.name (child table) to b.name (parent table). b.name does not have a unique constraint on it, so it may contain duplicate values.

If I put the name 'bob' in b.name twice, and then put it in a.name, I can no longer delete or update either instance of 'bob' in table b. In both cases, it complains that by deleting/updating the row in table b, the FKey linking a.name to b.name will fail.

Now this makes some sense, but I want to be able to tell the FKey to only fail if there is no other instance 'bob' in table b. So if I have multiple instances of 'bob' in b.name, I can change/delete any except for the last one.

[Note that doing the obvious and adding a unique constraint on b.name won't work because it will cause 'undefined behavior' in an application I didn't write]

Any ideas?

Answer 1

Straight from http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

...Additionally, MySQL and InnoDB require that the referenced columns be indexed for performance. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL.The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys....

So, keep your FKs tied to unique values or who's to say what happens?

Answer 2

As far as I remember foreign key should refer to a unique entity. In your case, you need to implement all logic in BEFORE triggers instead of using foreign keys

Answer 3

If you put 'bob' in b.name twice, then it no longer uniquely identifies as row in table b.

If your requirement is that b.name can have duplicate values, then your table a needs to use a better key (a surrogate key or a compound key).

Aside from the update/delete problem, queries such as SELECT * FROM a join b on a.name = b.name wouldn't work as expected either.

Answer 4

While your situation is far from optimal, or even recommended, you can get around the foreign key checks like this:

SET FOREIGN_KEY_CHECKS = 0;
// do your dirty work here
SET FOREIGN_KEY_CHECKS = 1;

You'll have to maintain referential integrity in your code as you described in your post.