In my noob opinion, it strikes me that in a serialized environment, this shouldn't be a problem.
Is that correct? If not, please explain to me why a foreign key's behavior on a non-unique column is not well defined.
Does behavior not well defined include that if a foreign key's referenced column is not unique then it will delete the keyed row even though there are valid entries?
Best Answer
You can't create a foreign key that targets a relation without a unique constraint in PostgreSQL. I'm somewhat astonished to hear that you can in MySQL.
It's really against the principle of a "foreign key" - if you can have multiple values, then the target is not in fact a key at all, it's just another data column.
That said, PostgreSQL isn't just preventing you from doing this for fun. AFAIK its foreign key implementation requires on unique b-tree indexes and cannot function properly without them. Unique indexes have some important properties, in particular regarding locking and transactional updates.
In theory I guess Pg could let you use any index, but it'd then have to deal with the mess of what happens when two concurrent transactions each delete one of two key rows. Each commit, individually, leaves the key relationship intact, but if both commit, one would have to trigger a cascade delete (which must happen before commit) or an error rollback. So PostgreSQL would have to serialize the transactions, forcing one to happen after the other, which would mean taking locks on all other rows with the same value for the key, irrespective of whether they're otherwise targeted by the query. Even then it wouldn't fully do the job because a concurrent insert could re-validate the key; Pg would really need to take predicate locks.
Implementation detail aside, what does a foreign key that targets a non-unique column actually gain you? Are you trying to model a non-optional m:n relationship?
If I had to do this, I'd do it with my own triggers, but I'd do it very carefully, as it's really hard to get it right.