如何在sql server中强制执行此约束
我有一个名为 city 的表和一个名为 city_city 的表。 city_city 关联两个城市记录，因此它有一个 fromcity_id 和一个 tocity_id。我可以通过唯一键对 fromcity_id 和 tocity_id 强制执行唯一性，但是如何强制执行唯一性，以便在 fromcity_id 和 tocity_id 颠倒的情况下无法插入记录。
例如，以下记录在概念上是相同的：
id fromcity_id tocity_id
1 100 200
2 200 100
I have a table called city, and a table called city_city. city_city correlates two city records, so it has a fromcity_id and a tocity_id. I can enforce uniqueness on fromcity_id and and tocity_id through a unique key, but how do I enforce uniqueness so that I cant insert a record if fromcity_id and tocity_id are reversed.
For example, the following records are conceptually the same:
id fromcity_id tocity_id
1 100 200
2 200 100
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
添加强制执行 (fromcity_id < tocity_id) 的检查约束，然后就完成了
Add a check constraint that enforces (fromcity_id < tocity_id) and you are all set
另一种选择是创建一个对视图具有唯一约束的索引视图（很糟糕，但也可以完成这项工作）。视图将类似于：
然后在视图上创建唯一索引：
问题是这与在每次插入时选择表的触发器相比是否具有更多、更少或大约相同的开销。答案是分析和测试您的工作负载、数据量和吞吐量。
Another option is to create an indexed view with a unique constraint on the view (horrid, but would also do the job). The view would be something like:
Then create a unique index on the view:
The question is whether this has more, less, or about the same overhead as a trigger that selects the table on each insert. The answer to this is to profile and test for your workload, data volumes and throughput.
这将需要一个触发器，因为您需要检查其他行。或者，您可以在应用程序层执行此操作。
This will require a trigger, because you need to examine other rows. Alernately, you could do this at the application layer.