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!