Opening
The example I’m going to use to illustrate how to enforce uniqueness on columns from different tables is a database for a house bidding application with the following schema:
Multiple houses (lots) can be listed in one auction. Users can place multiple bids on each lot. Auctions can be either open or sealed. In an open auction, users can see the highest bid and choose whether they want to outbid in real-time. In a sealed auction, all bids are placed “in blind.” When a sealed auction is finished, all bids are evaluated simultaneously, and the highest is declared the winner.
The requirement for this exercise is to enforce the following rules:
- In an open auction, two bids on the same lot can’t have the same amount
- In a sealed auction, two bids on the same lot with the same amount can exist
If the columns Lot, Amount and AuctionType were in the same table, we would create a simple unique index on dbo.Bid to enforce the requirements:
CREATE UNIQUE NONCLUSTERED INDEX [UIX_Bid]
ON dbo.Bid
(
Lot ASC,
Amount ASC
)
WHERE AuctionType = 'Open'
GO
Since the columns reside in different tables, we need to come up with a different solution.
Middlegame
I am convinced there are many solutions to this problem, but my favourite is using an Indexed View.
CREATE OR ALTER VIEW dbo.vUniqueBids
WITH SCHEMABINDING
AS
SELECT
b.BidId,
b.Amount,
b.Lot
FROM
dbo.Bid b
INNER JOIN
dbo.Lot l ON l.LotId = b.Lot
INNER JOIN
dbo.Auction a ON a.AuctionId = l.Auction
WHERE
a.AuctionType = 'Open';
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_vUniqueBids] ON dbo.vUniqueBids
(Lot, Amount)
GO
I’ve added some test data to test this solution works as expected:
As you can see in the image above, we have two auctions, one open and one sealed, and each auction has one lot allocated. The first user has placed a bid on each of the two lots. We will simulate a second user trying to bid on both lots with the same amount.
Trying to add a duplicate bid on the lot from the open auction fails, satisfying the first requirement.
Adding a duplicate bid on the lot allocated to the sealed auction completes successfully, satisfying our second requirement.
Endgame
While the same results may be achieved by using computed columns, a combination of UDFs with check constraints or even triggers, the indexed view option is, in my opinion, a more elegant solution. That being said, it has some restrictions, and ultimately, it depends on your use case.
When creating an indexed view, you must ensure that all the tables from the view definition are properly indexed (clustered indexes on PKs, nonclustered indexes on FKs, and supporting indexes for any filters). This is a best practice to avoid the performance penalty you may incur when modifying rows in these tables.
It can be observed in the example above that some DML operations on the underlying tables will now also modify data stored in the view’s clustered index.
Useful links:
