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: 258
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 :(
© Stack Overflow or respective owner