CODEWITHSUNDEEP
mysqlforeign-keys
dordal
dordal

Reputation: 633

MySQL: A foreign key referencing two parents

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?

Upvotes: 2

Views: 211

Answers (4)

Sonny
Sonny

Reputation: 8336

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.

Upvotes: 0

Seth
Seth

Reputation: 46453

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.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37382

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

Upvotes: 0

Stephen Fischer
Stephen Fischer

Reputation: 2546

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?

Upvotes: 2

Related Questions