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 |
+---------------+ +--------+ +---------------+--------+
| 2 | | 101 | | 1 | 101 |
+---------------+ +--------+ +---------------+--------+
| 102 | | 2 | 101 |
+--------+ +---------------+--------+
| 2 | 102 |
+---------------+--------+
There can't be another tag_bundle having exactly the same combination from tag 100 and tag 101. There can't be another tag_bundle having exactly the same combination from tag 101 and tag 102.
How can I ensure such unique constraint when executing SQL "concurrently"!! that is, to prevent concurrently adding two bundles with exactly 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 a unique column.
tag_bundle (unique on tags) tag tag_bundle_relation
+---------------+-----------+ +--------+ +---------------+--------+
| tag_bundle_id | tags | | tag_id | | tag_bundle_id | tag_id |
+---------------+-----------+ +--------+ +---------------+--------+
| 1 | "100,101" | | 101 | | 1 | 101 |
+---------------+-----------+ +--------+ +---------------+--------+
| 100 | | 1 | 100 |
+--------+ +---------------+--------+
but it seems not a good way :(