Suppose a business rule stipulates that a certain condition should never exist, and you can’t enforce it with foreign keys. An INSTEAD OF trigger might be the answer, but such a trigger is often more hassle than it's worth. One solution is to use a view with a unique index in conjunction with a dummy table that contains two rows.

Imagine you have the following two tables, Region and Office:

Region

 Office

(See Region and Office table create and insert script in the Resources section for code to create and populate these tables.)

How can you prevent an Active office from belonging to an Inactive Region? 

The tables above have some referential integrity. RegionId is a foreign key in the Office table. Check constraints ensure that the IsActive flag never equals true if ExpirationDate is not null. But how can we prevent an active office from residing in an inactive region? The tables could be redesigned or an INSTEAD OF trigger could be written. But a less intrusive technique is to use a view with a unique index in conjunction with a two-row table.

 DuplicateRows table

The DuplicateRows table contains two rows:

(See DuplicateRows - Create and Insert script in the Resources section to create and populate this table.)

The only requirement for this table is that it contains at least two rows. Its structure and content is not important. It could be a single column table with different values for each row. The advantage of using a dedicated table with the name "DuplicateRows" with "Duplicate Row" in its contents is that its purpose will be more easily remembered and hence less likely to be deleted by an over-zealous or forgetful database administrator.

 InvalidRegionIsInactiveButOfficeIsActiveView View

The business rule is that if the Region is inactive, no Offices that are assigned to it should be active. In other words, you can't close down the Antarctica region until Byrd Station becomes inactive or is assigned to another region. The following view displays regions where the Region’s IsActive flag is False (0), but the corresponding Office’s IsActive flag is True (1). This is a condition that contradicts the business rule. The Cross Join of dbo.DuplicateRows, which contains two rows, ensures that if a region satisfies the where clause the results will contain at least two rows.  (Note that the view includes the phrase “With SchemaBinding.” An index on a view can’t be created without it.)

CREATE View dbo.InvalidRegionIsInactiveButOfficeIsActiveView With SchemaBinding
As
SELECT     dbo.Region.RegionId
FROM       dbo.Region
INNER JOIN dbo.Office
ON         dbo.Region.RegionId = dbo.Office.RegionId
CROSS JOIN dbo.DuplicateRows
WHERE      dbo.Region.IsActive = Convert(bit, 0)
AND        dbo.Office.IsActive = Convert(bit, 1)

The final piece of the puzzle is the view's unique index. A unique index prevents the return of duplicate rows. But the view's query is cross joined with a two-row table. The consequence of this is that if the query returns any rows, it will always return duplicate rows. But the unique index ensures that this will never happen.

CREATE UNIQUE CLUSTERED INDEX IX_RegionInvalidOfficeIsActiveView_RegionId ON dbo.InvalidRegionIsInactiveButOfficeIsActiveView
 (RegionId ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The end result is that the business rule will never be violated. 

Test: Closing Down the Antarctica Region

The heartless folks at HQ decide to shut-down the Antarctica Region. The following statement tries to update IsActive to False (0) regardless of the Office data:

UPDATE dbo.Region
 SET dbo.Region.IsActive  = Convert(bit, 0),
     dbo.Region.ExpirationDate = GetDate()
 WHERE dbo.Region.RegionName  = N'Antarctica'

When the statement is executed, the following error occurs:

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.InvalidRegionIsInactiveButOfficeIsActiveView' with unique index 'IX_RegionInvalidOfficeIsActiveView_RegionId'. The duplicate key value is (5).

The statement has been terminated.

The Byrd Station office needs to be either made inactive or assigned to a different office before the Antarctica region can be made inactive. I don't want to fire anybody, so I'll reassign them to the South region:

UPDATE dbo.Office
 SET dbo.Office.RegionId = (SELECT dbo.Region.Regionid 
                             FROM dbo.Region 
                             WHERE dbo.Region.RegionName = N'South'
                            )
 FROM dbo.Office
 WHERE dbo.Office.RegionId =(SELECT dbo.Region.Regionid
                              FROM dbo.Region
                              WHERE dbo.Region.RegionName = N'Antarctica'
                             )

Once there are no offices assigned to Antarctica, the Antarctica region can be made inactive with the Update statement that failed earlier.

How to implement this technique in your database

Use the following steps to implement this technique in your database.

1. Create and populate the dbo.DuplicateRows table (only need to do this once).
2. Write a query that would return rows if your business rule is violated.
3. Add a Cross Join of dbo.DuplicateRows to the query.
4. Create a view with the query using the With SchemaBinding clause.
5. Create a unique index on the view.

Conclusion

Using an indexed view in conjunction with a two-row table might not be the most efficient technique to enforce business rules, but it does have the benefit of eliminating the need for complicated INSTEAD OF triggers. This solution wouldn't be needed if Microsoft offered the option of writing a BEFORE trigger that allowed a command to be canceled when a rule is violated. The technique described above (which I’ve dubbed the “Mary Hartman, Mary Hartman” solution in honor of the seventies soap opera parody) can be thought of as a poor-man’s BEFORE trigger.