Check Constraint - Subqueries Are Not Allowed In This Context
Answer : SQL Server does not currently support subqueries for CHECK CONSTRAINTs. As you have discovered, there can be trouble with CHECK constraints involving UDFs when attempting to circumvent the subquery limitation. The alternative constraint implementation strategies are triggered procedural and embedded procedural . The former is preferred because, in common with declarative constraints, they cannot be circumvented. Implementing a triggered procedural strategy that is well optimized and handles concurrency issues is non-trivial but still doable. I highly recommend the book Applied Mathematics for Database Professionals By Lex de Haan, Toon Koppelaars, chapter 11 (the code examples are Oracle but can be easily ported to SQL Server). As others have mentioned already, this type of Check constraints is not yet implemented in SQL-Server. Besides triggers, you could also examine the possibility of changing the table's design. A possible alternative includes storing the...