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:
- Create the ITEM_IN_BOX and ITEM_IN_TRAY tables using the script in Listing 1 (below).
- Use Listing 2 (below) to insert violating data into the ITEM_IN_BOX and ITEM_IN_TRAY tables and identify it.
- Create an indexed view using Listing 3 (below).
- Rerun your inserts using Listing 4 (below) to see how the indexed view rejects the violating row. This view will always be empty.
- Use Listing 5 (below) 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.
-- -- 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 ) GO ALTER TABLE ITEM_IN_BOX ADD CONSTRAINT IIB_PK PRIMARY KEY CLUSTERED (BOX_ID, ITEM_ID) GO ALTER 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 ) GO ALTER TABLE ITEM_IN_TRAY ADD CONSTRAINT IIT_PK PRIMARY KEY CLUSTERED (TRAY_ID, ITEM_ID) GO ALTER 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 ) GO INSERT 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 1
INSERT INTO ITEM_IN_BOX(ITEM_ID, BOX_ID) VALUES (1, 1) -- OK
INSERT INTO ITEM_IN_BOX(ITEM_ID, BOX_ID) VALUES (2, 1) -- OK
-- Add item 3 to both Box 2 & Tray 2 - violating our business rule
INSERT INTO ITEM_IN_BOX (ITEM_ID, BOX_ID) VALUES (3, 2) -- OK
INSERT INTO ITEM_IN_TRAY(ITEM_ID, TRAY_ID) VALUES (3, 2) -- Violating
-- Find the violating items (duplicate by 2)
SELECT IIB.ITEM_ID
FROM dbo.ITEM_IN_TRAY IIT
INNER JOIN dbo.ITEM_IN_BOX IIB
ON IIT.ITEM_ID = IIB.ITEM_ID
CROSS JOIN dbo.NUMS
WHERE NUMS.NUM <= 2;
/*
ITEM_ID
-----------
3
3
(2 row(s) affected)
*/
-- Cleanup before proceeding DELETE FROM ITEM_IN_TRAY; DELETE FROM ITEM_IN_BOX; DELETE FROM ITEM; GO CREATE VIEW ITEM_IN_TRAY_OR_BOX_V(ITEM_ID) WITH SCHEMABINDING AS ( SELECT IIB.ITEM_ID FROM dbo.ITEM_IN_TRAY IIT INNER JOIN dbo.ITEM_IN_BOX IIB ON IIT.ITEM_ID = IIB.ITEM_ID CROSS JOIN dbo.NUMS WHERE NUMS.NUM <= 2 ) GO CREATE 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 1 INSERT INTO ITEM_IN_BOX(ITEM_ID, BOX_ID) VALUES (1, 1) -- OK INSERT INTO ITEM_IN_BOX(ITEM_ID, BOX_ID) VALUES (2, 1) -- OK -- Add item 3 to both Box 2 & Tray 2 - violating our business rule INSERT INTO ITEM_IN_BOX (ITEM_ID, BOX_ID) VALUES (3, 2) -- OK INSERT 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 10 Cannot 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_V DROP TABLE NUMS DROP TABLE ITEM_IN_TRAY DROP TABLE ITEM_IN_BOX DROP TABLE ITEM
