CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-design
Dai
Dai

Reputation: 155250

Adding a unique constraint for a relationship 2 degrees away

Consider this database with 4 tables (primary-keys in asterisks):

Products( *ProductId*, SkuText, ... )
ProductRevisions( ProductId, *RevisionId*, ... )
Orders( *OrderId*, ... )
OrderItems( *OrderId*, *ProductRevisionId*, Quantity, ... )

The idea being that a Product SKU can have multiple revisions (e.g. a 2016 version of a product compared to its 2015 version). The business rules are such that an Order for a Product can only have a single ProductRevision, e.g. an order cannot request both the 2014 and 2016 versions of the same product, they can only have the 2014 or 2016 version.

Ordinarily this wouldn't be a problem: the OrderItems table would have a ProductId column with a UNIQUE constraint on OrderId and ProductId. However because OrderItems's references ProductRevisionId (so the reference to the ProductId is indirect) it means a simple UNIQUE constraint fails and the schema would accept the following data, even though it is invalid as-per the business rules:

Products
ProductId, SkuText
        1, 'Kingston USB Stick'

ProductRevisions
ProductId, RevisionId, ...
        1,          1, '2014 model'
        1,          2, '2016 model'

Orders
OrderId
        1

OrderItems
OrderId, ProductRevisionId, Quantity
      1,                 1,      100
      1,                 2,       50 -- Invalid data! Two revisions of the same Product should not be in the same order.

What I need is something like this:

ALTER TABLE OrderItems
    ADD CONSTRAINT UNIQUE ( OrderId, SELECT ProductId FROM ProductRevisions WHERE RevisionId = OrderItems.ProductRevisionId )

I don't want to denormalize my OrderItems table by adding an explicit ProductId column because that adds a potential point of failure if the parent/child relationship between a given ProductId and ProductRevisionId were to change then the data becomes invalid.

What are my options?

Upvotes: 1

Views: 59

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

You can create an indexed view to enforce the constraint. In your case, it'd be something like:

create view [OrderItemProductRevisions]
with schemabinding
as
   select oi.OrderID, pr.ProductID
   from dbo.OrderItems as oi
   join dbo.ProductRevisions as pr
      on oi.ProductRevisionID = pr.ProductRevisionID
go
create unique clustered index [CUIX_OrderItemProductRevisions]
   on [OrderItemProductRevisions] (OrderID, ProductID)
go

Now, if you try to add two revisions of the same product to the same order, you should violate the unique index on the view and it will be disallowed.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is really more of a comment, but it is too long.

One option is to create a trigger. This allows you to validate the data using any rules that you want. However, triggers are cumbersome and unnecessary.

Another option is essentially what you say: include both Product and ProductRevision in OrderLines. However, this doesn't quite solve the problem. You need to ensure that the product actually matches the product on the revision.

I am thinking that the best option might be to have a Revision column in ProductRevisions. So, this table would have:

  • ProductRevisionId -- primary key for the table
  • ProductId
  • RevisionId
  • unique constraint on (ProductId, RevisionId)

The foreign key constraint in OrderLines can then have two columns in it -- (ProductId, RevisionId). Then a unique constraint on (OrderId, ProductId) ensures only one revision.

The downside to this method is that a product can only appear on only one line in each order. However, you don't need triggers.

Upvotes: 1

Related Questions