Re: CHECK Constraints

From: Mikito Harakiri <mikharakiri_at_iahu.com>
Date: Wed, 22 Dec 2004 13:02:09 -0800
Message-ID: <Dslyd.22$Tg5.58_at_news.oracle.com>


"Brandon Lilly" <avarice_at_nospam_swbell.net> wrote in message news:8967EF43-2630-421C-88E2-5069EB533ADC_at_microsoft.com...
> You COULD accomplish this in a check constraint
> (http://www.winnetmag.com/Article/ArticleID/22830/22830.html)
>
> But you would be better off writing a trigger instead, I think.

There is a difference between UDF and subquery in the check constraint. Subquery could be unnested so that check constraint becomes global condition upon some view. It's no longer row-based. A subquery within UDF, however, can't be transformed, as no SQL engine would dare to look inside UDF. Therefore, a check constraint with UDF is a half of constraint, it checks the condition from one table, but totally ignores constraint violation when updates happen in the tables mentioned within subquery.

http://www.dbazine.com/tropashko8.shtml

I'm not sure if ANSII standard (which seem to allow subqueries in check constraints) says anything about subquery unnesting or is even aware of the issue. Received on Wed Dec 22 2004 - 22:02:09 CET

Original text of this message