0

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!

Jesse Q
  • 1,451
  • 2
  • 13
  • 18

1 Answers1

1

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;

Belayer
  • 13,578
  • 2
  • 11
  • 22
  • This solution absolutely solved the most important portion of my question - the database constraint. Thank you for taking the time to comment! – Jesse Q Jun 24 '20 at 15:30