EF Core:为任意顺序的两列创建唯一约束 [英] EF Core: Create Unique Constraint for Two Columns, Any Order

问题描述

我在一张表中有两列，Rates:

I have two columns in one table, Rates:

ZonePointAId
ZonePointBId

在引用 Zones 表中单个 ZoneId 列的两列上使用外键.无论找到值的顺序如何，我都想强制执行约束.(不管哪个先列出，区之间的速率都是一样的)例如:

With a foreign key on both of the columns that reference a single ZoneId column in the Zones table. I'd like to enforce a constraint regardless of the order the values are found. (The rate between the zones is the same regardless of which is listed first) For example:

ZonePointAId = 63, ZonePointBId = 64  - Pass!
ZonePointAId = 63, ZonePointBId = 63  - Pass!
ZonePointAId = 64, ZonePointBId = 63  - Fail!

我已经尝试了这里列出的解决方案 独特实体框架中多列的键约束，但它们都允许插入逆序.我正在使用 Postgres(如果重要，则使用 RDS)..NET 核心 3.1.

I've tried the solutions listed here in this Unique Key constraints for multiple columns in Entity Framework but they all allow the inverses to be inserted. I'm using Postgres (RDS if it matters). .NET Core 3.1.

我试图避免为每个反向源/目标组合复制数据.任何见解将不胜感激.我什至不确定数据库是否有能力做这样的事情，更不用说通过注释或 Fluent 定义的 EF Core，但我想如果有人知道它会是你们！

I am trying to avoid duplicating the data for every inverse source/dest combination. Any insight would be greatly appreciated. I'm not even sure the database is capable of such a thing, much less EF Core of defining via annotations or Fluent, but I figured if anyone knew it would be you guys!

推荐答案

好吧，至少在数据库级别，您可以根据表达式创建唯一索引.在这种情况下，使用最小和最大函数对值进行排序":

Well at least at the database level, you can create an unique index based on expressions. In this case use the least and greatest functions to 'order' the values:

create unique index AidBid_sameas_BidAid
    on rates (least(ZonePointAId,ZonePointBId), greatest(ZonePointAId,ZonePointBId)); 

参见 fiddle;

这篇关于EF Core:为任意顺序的两列创建唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！