CODEWITHSUNDEEP
postgresql.net-coreentity-framework-core
Jesse Q
Jesse Q

Reputation: 1661

EF Core: Create Unique Constraint for Two Columns, Any Order

I have two columns in one table, Rates:

ZonePointAId
ZonePointBId

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!

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.

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!

Upvotes: 0

Views: 418

Answers (1)

Belayer
Belayer

Reputation: 14861

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));

See fiddle;

Upvotes: 1

Related Questions