Michael J. Swart

Well, the title is a bit misleading. It’s not possible really. At least SQL Server doesn’t support any syntax that will allow a constraint to be created directly on a view. But there is a little bit of a workaround and it’s far from perfect. It involves making a UNIQUE constraint do the work of a CHECK constraint.

Using the AdventureWorks database for illustration. Lets say you wanted to enforce the ridiculous business rule that no-one with the middle initial ‘Q’ is allowed to be a Production Technician. We’ll call this business rule “No Q Technicians”.

Here’s a query that will return any offending violations of the rule:

SELECT * 
FROM HumanResources.Employee e
JOIN Person.Person p
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE MiddleName = 'Q'
    AND JobTitle LIKE 'Production Technician'

What we can do is turn that into a indexed view and use the unique constraint to prevent rows in the table:

CREATE VIEW Person.v_NoQTechnicians WITH schemabinding
AS
SELECT 1 one
FROM HumanResources.Employee e
JOIN Person.Person p
   ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID = 1 -- we want exactly one placeholder row in this view always
   OR (MiddleName = 'Q'
   AND JobTitle LIKE 'Production Technician%')
GO

CREATE UNIQUE CLUSTERED INDEX ix_NoQTechnicians ON Person.v_NoQTechnicians(one)
GO

UPDATE Person.Person
SET MiddleName = 'Q'
WHERE BusinessEntityID = 42
GO

The last update statement will fail with an error message that looks like:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'Person.v_NoQTechnicians' with unique index 'ix_NoQTechnicians'.
The statement has been terminated.

There’s a lot that is wrong with the solution. For one thing, I think it’s a big hack. It felt like it while I was writing it. I also don’t like having to accommodate the one placeholder row in the view. Another thing is that indexed views are very restrictive with respect to the variety of select commands that are allowed.

Any improvements are welcome. Leave a note in the comments!