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:

1. Create the ITEM_IN_BOX and ITEM_IN_TRAY tables using the script in Listing 1.
2. Use Listing 2 to insert violating data into the ITEM_IN_BOX and ITEM_IN_TRAY tables and identify it.
3. Create an indexed view using Listing 3.
4. Rerun your inserts using Listing 4 to see how the indexed view rejects the violating row. This view will always be empty.
5. Use Listing 5 to drop all the objects.

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.