Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
If you dont care about cascading duplicates you can use a foreign key on non unique column.I have never seen something like this used in production but who knows.. –  Mihai Feb 14 at 21:53
    
Also,Postgres might not allow it,I know about Mysql`s InnoDB. –  Mihai Feb 14 at 22:10
    
Please describe this in more detail - show a sequence of operations you think should work, and the error when it doesn't. Explain what you would expect to happen instead. (Edit the question, comment here when done). –  Craig Ringer Feb 14 at 22:40
2  
Postgres does not allow FKs to be created unless they reference columns with unique constraint. –  ypercube Feb 15 at 1:11

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.