unique constraint (w/o Trigger) on "one-to-many" relation
- by elgcom
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 :(
