“一对多”上的唯一约束（无触发器）关系
为了说明问题，我举一个例子：
一个tag_bundle由一个或多个标签组成。 唯一的标签组合可以映射到唯一的tag_bundle，反之亦然。
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 |
+---------------+--------+
不可能有另一个 tag_bundle 具有与标签 100 和标签 101 完全相同的组合。 不可能有另一个 tag_bundle 具有与标签 101 和标签 102 完全相同的组合。
在“并发”执行 SQL 时如何确保这样的唯一约束！ 也就是说，为了防止同时添加两个具有完全相同相同标签组合的捆绑包，
在任何表上添加简单的唯一约束是行不通的， 除了触发器或显式锁定之外还有其他解决方案吗？
我只想到了这个简单的方法：将标签组合成字符串，并让它成为一个唯一的列。
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 |
+--------+ +---------------+--------+
但这似乎不是一个好方法:(
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 :(
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
为什么要有“无触发器”的限制？有了它，再加上一些数据复制，您就可以得到您需要的东西。将解决方案中的“标签”字段更改为 INTEGER 的数组字段（或 tag_id 的任何类型）
虽然认识到该解决方案的令人不快，但我没有看到解决方法。虽然我会使用数组而不是字符串作为“标签”，但将其放在与 tag_bundle 不同的表中，仍然使其唯一并在 tag_bundle_relation 上放置触发器以使用 array_agg(tag_id) (>8.4) 更新标签字段，如果失败，则触发器更新失败。
Why the constraint of 'without a trigger'? With it, combined with a bit of data duplication, you can get what you need. Change your 'tags' field in your solution to an array field of INTEGERs (or whatever type tag_id is)
While recognising the unpleasantness of the solution, I don't see a way round it. Though I would use an array instead of a string for 'tags', put it in a separate table from tag_bundle, still make it unique and put a trigger on tag_bundle_relation to update the tags field with array_agg(tag_id) (>8.4), and if that fails, fail the trigger update.
为了在多个事务更新表时正常工作，您需要创建一个可延迟的、初始延迟的 约束触发器.
In order to work correctly when multiple transactions will be updating the tables, you will need to create a deferable, initially deferred, constraint trigger.