SQL Server: Implementing constraints with indexed-views

Indexed-views are useful for more than just performance improvement, they can also be used to implement constraints. In particular, constraints involving multiple tables.

The principle is relatively simple. Design an indexed-view such that duplicate keys are selected for every constraint violation. Sometimes a “numbers” table can be useful to double-up the rows (although we won’t be using that technique here).

An example using AdventureWorks may be helpful… This is a little contrived but let’s say that we wanted to setup a constraint such that finished goods may be supplied by only one preferred vendor.

First of all, we need to update the data so that the constraint is not violated (it is with the data as supplied):

UPDATE V
    SET PreferredVendorStatus = 0
    FROM Purchasing.Vendor V
        JOIN (
            SELECT P.ProductID, VendorID = MIN(V.VendorID)
                FROM Production.Product P
                    JOIN Purchasing.ProductVendor PV ON P.ProductID = PV.ProductID
                    JOIN Purchasing.Vendor V ON PV.VendorID = V.VendorID
                WHERE P.FinishedGoodsFlag = 1
                    AND V.ActiveFlag = 1
                    AND V.PreferredVendorStatus = 1
                GROUP BY P.ProductID
                HAVING COUNT(*) > 1
        ) U ON V.VendorID = U.VendorID

Then we create an indexed-view to represent the constraint:

CREATE VIEW Purchasing.OnePreferredVendorForFinishedGoods
WITH SCHEMABINDING
AS
SELECT P.ProductID, V.VendorID, _Count = COUNT_BIG(*)
    FROM Production.Product P
        JOIN Purchasing.ProductVendor PV ON P.ProductID = PV.ProductID
        JOIN Purchasing.Vendor V ON PV.VendorID = V.VendorID
    WHERE P.FinishedGoodsFlag = 1
        AND V.ActiveFlag = 1
        AND V.PreferredVendorStatus = 1
    GROUP BY P.ProductID, V.VendorID

If a particular ProductID occurs more than once in the view output, it means we have a finished product that it is supplied by more than one preferred vendor. We add a unique index to prevent such an occurrence:

CREATE UNIQUE CLUSTERED INDEX IX__OnePreferredVendorForFinishedGoods__ProductID ON Purchasing.OnePreferredVendorForFinishedGoods (ProductID)

Now, let’s try to violate the constraint:

UPDATE Purchasing.Vendor
    SET PreferredVendorStatus = 1
    WHERE VendorID = 55

We see the following:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘Purchasing.OnePreferredVendorForFinishedGoods’ with unique index ‘IX__OnePreferredVendorForFinishedGoods__ProductID’.

The statement has been terminated.

This is a powerful technique for implementing complicated constraints. Another good thing is that this works in all editions, even Express Edition.

Comments are closed.