Enforce Multitable Constraints Using Indexed Views

There are cases in which you need to enforce business rules between multiple tables. One such case is when validating that a custom sequence, used as a primary key in two tables, isn't used more than once. In such cases, CHECK constraints are limited to a single table. Triggers can be used but provide a non-optimal solution because you need to create a trigger on both tables. So how do you do so then? By using indexed views to implement multitable constraints.

Implementing Indexed Views

Consider a scenario in which you have an inventory control system for managing items. Each item can be stored in either a tray or a box, but not in both at the same time, obviously. Figure 1 shows a simplified schema that holds the data.

The ITEM table holds all items in the system, and the referencing tables ITEM_IN_BOX and ITEM_IN_TRAY each hold the location of an item, in a box or in a tray, respectively. For the sake of simplicity, additional columns aren't shown.

To enforce the business rule that says the same ITEM_ID can’t be inserted into both ITEM_IN_BOX and ITEM_IN_TRAY, you could add INSTEAD OF INSERT and UPDATE triggers to both ITEM_IN_BOX and ITEM_IN_TRAY tables, validating this logic. However, an alternative that’s much easier to maintain is to use an indexed view.

Indexed views are typically used for aggregations, but work well for enforcing multitable constraints because they allow joining tables and enforce uniqueness on the result set. Unlike standard views, which hold only the underlying SQL query and are replaced with this query upon execution, an indexed view is materialized with the query’s data. This means that when the underlying tables’ data is modified, the view is updated. When you query the view, all the data has already been calculated. You index a view by creating a unique clustered index on it. (Indexed views are available in SQL Server 2000 and later.)

The indexed view will join ITEM_IN_BOX and ITEM_IN_TRAY on ITEM_ID. If the same ITEM_ID is found in both tables, the ITEM_ID will be returned by the underlying query of the indexed view. Because you want to reject such a case, you need a way to duplicate this row more than once so that the unique index will fail the insert/update. To do so, you’ll want to join the two tables to an additional NUMS table that has only two rows in it and is used solely for multiplying the output by 2 (two rows instead of one per violating ITEM_ID). Now the uniqueness of the view will reject the duplicated rows.

The following steps walk you through implementing indexed views to enforce multitable constraints:

Enforce Multitable Constraints More Easily

This common database task doesn’t have to be difficult. Indexed views and the technique for the duplication of rows using NUMS tables offer a great solution for enforcing business rules between multiple tables.

---- ITEM--CREATE TABLE ITEM(   ITEM_ID INT PRIMARY KEY NOT NULL)  GO---- ITEM_IN_BOX--CREATE TABLE ITEM_IN_BOX (    BOX_ID  INT NOT NULL,   ITEM_ID INT NOT NULL)  GOALTER TABLE ITEM_IN_BOX ADD CONSTRAINT IIB_PK PRIMARY KEY CLUSTERED (BOX_ID, ITEM_ID)GOALTER TABLE ITEM_IN_BOX ADD CONSTRAINT IIB_I_FK FOREIGN KEY (ITEM_ID) REFERENCES ITEM (ITEM_ID)GO---- ITEM_IN_TRAY--CREATE TABLE ITEM_IN_TRAY (    TRAY_ID INT NOT NULL,   ITEM_ID INT NOT NULL)  GOALTER TABLE ITEM_IN_TRAY ADD CONSTRAINT IIT_PK PRIMARY KEY CLUSTERED (TRAY_ID, ITEM_ID)GOALTER TABLE ITEM_IN_TRAY ADD CONSTRAINT IIT_I_FK FOREIGN KEY (ITEM_ID) REFERENCES ITEM (ITEM_ID)GO---- NUMS--CREATE TABLE NUMS(   NUM INT NOT NULL)  GOINSERT INTO NUMS(NUM) VALUES(1);INSERT INTO NUMS(NUM) VALUES(2);GO

INSERT INTO ITEM(ITEM_ID) VALUES (1)INSERT INTO ITEM(ITEM_ID) VALUES (2)INSERT INTO ITEM(ITEM_ID) VALUES (3)-- Add item 1 & item 2 to Box 1INSERT INTO ITEM_IN_BOX(ITEM_ID, BOX_ID)   VALUES (1, 1)-- OKINSERT INTO ITEM_IN_BOX(ITEM_ID, BOX_ID)   VALUES (2, 1)-- OK-- Add item 3 to both Box 2 & Tray 2 - violating our business ruleINSERT INTO ITEM_IN_BOX (ITEM_ID, BOX_ID)  VALUES (3, 2)-- OKINSERT INTO ITEM_IN_TRAY(ITEM_ID, TRAY_ID) VALUES (3, 2)-- Violating-- Find the violating items (duplicate by 2)SELECT IIB.ITEM_IDFROM dbo.ITEM_IN_TRAY IIT  INNER JOIN dbo.ITEM_IN_BOX IIB    ON IIT.ITEM_ID = IIB.ITEM_ID  CROSS JOIN dbo.NUMSWHERE NUMS.NUM <= 2;/*ITEM_ID-----------33(2 row(s) affected)*/

-- Cleanup before proceedingDELETE FROM ITEM_IN_TRAY;DELETE FROM ITEM_IN_BOX;DELETE FROM ITEM;GOCREATE VIEW ITEM_IN_TRAY_OR_BOX_V(ITEM_ID)WITH SCHEMABINDINGAS(SELECT IIB.ITEM_IDFROM dbo.ITEM_IN_TRAY IIT  INNER JOIN dbo.ITEM_IN_BOX IIBON IIT.ITEM_ID = IIB.ITEM_ID  CROSS JOIN dbo.NUMSWHERE NUMS.NUM <= 2)GOCREATE UNIQUE CLUSTERED INDEX ITEM_IN_TRAY_OR_BOX_V_UI ON ITEM_IN_TRAY_OR_BOX_V(ITEM_ID)GO

INSERT INTO ITEM(ITEM_ID) VALUES (1)INSERT INTO ITEM(ITEM_ID) VALUES (2)INSERT INTO ITEM(ITEM_ID) VALUES (3)-- Add item 1 & item 2 to Box 1INSERT INTO ITEM_IN_BOX(ITEM_ID, BOX_ID)   VALUES (1, 1)-- OKINSERT INTO ITEM_IN_BOX(ITEM_ID, BOX_ID)   VALUES (2, 1)-- OK-- Add item 3 to both Box 2 & Tray 2 - violating our business ruleINSERT INTO ITEM_IN_BOX (ITEM_ID, BOX_ID)  VALUES (3, 2)-- OKINSERT INTO ITEM_IN_TRAY(ITEM_ID, TRAY_ID) VALUES (3, 2)-- Should FAIL/*(1 row(s) affected)...(1 row(s) affected)Msg 2601, Level 14, State 1, Line 10Cannot insert duplicate key row in object 'dbo.ITEM_IN_TRAY_OR_BOX_V' with unique index 'ITEM_IN_TRAY_OR_BOX_V_UI'.The statement has been terminated.*/

DROP VIEW ITEM_IN_TRAY_OR_BOX_VDROP TABLE NUMSDROP TABLE ITEM_IN_TRAY DROP TABLE ITEM_IN_BOX DROP TABLE ITEM