unique constraint (w/o Trigger) on "one-to-many" relation

Posted by elgcom on Stack Overflow See other posts from Stack Overflow or by elgcom
Published on 2010-04-20T21:30:12Z Indexed on 2010/04/20 21:33 UTC
Read the original article Hit count: 264

Filed under:

unique

|

atomicity

|

sql

|

postgresql

|

one-to-many

To illustrate the problem, I make an example:

A tag_bundle consists of one or more than one tags. A unique tag combination can map to a unique tag_bundle, vice versa.

 tag_bundle                   tag            tag_bundle_relation
 +---------------+        +--------+      +---------------+--------+
 | tag_bundle_id |        | tag_id |      | tag_bundle_id | tag_id |
 +---------------+        +--------+      +---------------+--------+
 |       1       |        | 100    |      |       1       |  100   |
 +---------------+        +--------+      +---------------+--------+
                          | 101    |      |       1       |  101   |
                          +--------+      +---------------+--------+

There can't be another tag_bundle having the combination from tag 100 and tag 101.

How can I ensure such unique constraint when executing SQL "concurrently"!! that is, to prevent concurrently adding two bundles with the same tag combination

Adding a simple unique constraint on any table does not work, Is there any solution other than Trigger or explicit lock.

I come to only this simple way: make tag combination into string, and let it be unique.

tag_bundle  (unique on tags)         tag            tag_bundle_relation
 +---------------+--------+      +--------+      +---------------+--------+
 | tag_bundle_id |  tags  |      | tag_id |      | tag_bundle_id | tag_id |
 +---------------+--------+      +--------+      +---------------+--------+
 |       1       | 100,101|      | 100    |      |       1       |  100   |
 +---------------+--------+      +--------+      +---------------+--------+
                                 | 101    |      |       1       |  101   |
                                 +--------+      +---------------+--------+

but it seems not a good way :(

Developer IT