MCTS 70-433 Check Constraints - Logical Operators and Sub-queries

Coming from an Oracle background I tend to think of check contraints as simpler checks. I'm pretty sure a subquery isn't allow by Oracle in a check constraint (ORA-02251: subquery not allowed here).

The SQL Server check constraint clause is more open/powerful:

In the Check Constraints dialog box, type an expression in the Check Constraint Expression dialog box using the following syntax:

{constant

column_name

function

(subquery)}

[{operator

AND

OR

NOT}

{constant

column_name

function

(subquery)}]

http://msdn.microsoft.com/en-us/library/58xz3zdd.aspx

The following extract illustrates the limitation of check constraints in Oracle:

ALTER TABLE order

ADD CONSTRAINT ck_fk CHECK( 

   0 < (select count(1) from business_customer b 

        where b.cust_id=order.cust_id)

   OR  

   0 < (select count(1) from home_customer h

        where h.cust_id=order.cust_id)  

)

Unfortunately, the RDBMS that I use leaves no doubt that, today, this is not possible:

ORA-02251: subquery not allowed here

ALTER TABLE emp

ADD CONSTRAINT ck_fk CHECK( 

   0 < (select count(1) from dept 

        where dept.deptno=emp.deptno) 

)

What about User Defined SQL functions (UDF)? Like subquery, UDF returns a scalar value as well, which is normally allowed in any place of expression where ordinary scalar variable or constant is allowed. However, if a RDBMS of my choice were allowing UDF calls within check constraint expression, then I could claim that the implementation is inconsistent. Indeed, UDF can contain embedded SQL inside its body:

function deptno_cnt( deptno integer ) 

RETURN integer IS 

   ret_val integer; 

BEGIN 

   select count(1) into ret_val from dept  

   where dept.deptno = deptno

   RETURN ret_val; 

END;

http://www.dbazine.com/oracle/or-articles/tropashko8