I have this problem in which there is a link between two entities that could exist for a certain time, the relationship can be repeated multiple times during different dates.
How can I create a unique constraint in the database, based on the time of link?
Example, users owning cars:
UserID | CarID | DatePurchase | DateSell
-------+-------+--------------+---------
user1 | car1 | 2020-1-1 | 2020-6-1
user2 | car1 | 2020-6-1 | 2021-1-1
user1 | car1 | 2021-1-1 | NULL
In the above example, the car CAN NOT be owned by more than one user at the same time.
If I add a unique constraint on both UserID & CarID this will not allow the user to own the car more than once.
I also can't include the start date in the unique constraint as it will not fix the problem obviously if there were interlaps in the dates.
I don't want to leave such important rule to the business logic. I prefer to enforce such rules in the database, is there anyway I can add such a constraint at the database level?