Home Dashboard Directory Help
Search

Support subqueries for CHECK CONSTRAINTs by AaronBertrand


Status: 

Closed
 as Won't Fix Help for as Won't Fix


7
1
Sign in
to vote
Type: Suggestion
ID: 340497
Opened: 4/25/2008 6:28:48 PM
Access Restriction: Public
0
Workaround(s)
view

Description

Not all that much in Google groups about Msg 1046, but I still have a hard time understanding the restriction that you can't use a subquery in a check constraint. If this for performance reasons? Because if you can use a scalar UDF, I promise you that I can build one that performs very poorly.

This came up because someone wanted to ensure that a column value existed in metadata (e.g. CHECK table_name IN (SELECT name FROM sys.tables)). You can't have a subquery (or a table-valued function) in a check constraint, and of course you can't place a foreign key against a system view. So their only alternative now is a trigger. It's a workaround, but it's not perfect.
Details
Sign in to post a comment.
Posted by Microsoft on 2/1/2012 at 9:13 AM
Dear Aaron,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Thank you,
Joachim Hammer
Posted by Corey Furman on 11/19/2008 at 5:46 AM
Wrapping the subquery in a UDF works around the original stated problem, but does not negate the check constraint limitations. See "Limitations of CHECK Constraints" at http://msdn.microsoft.com/en-us/library/ms188258.aspx. I encountered this situation when my design called for a foreign key relationship on a column in one table to a value existing in one of two other tables. This cannot be done with a foreign key relationship, and because Check Constraints do not operate like foreign keys, you do not always get the same behavior.

Posted by Microsoft on 4/28/2008 at 12:54 PM
Dear Aaron

This is a general restriction of SQL at the moment that subqueries are often not allowed in scalar expression context (another one is Group by).

The standard workaround is to wrap the expression into a T-SQL UDF that encapsulates the subquery.

We currently recommend the UDF workaround, but I will keep the bug open for a future release and to gather more feedback from your side.

Thanks
Michael
Sign in to post a workaround.