sqlcheck-constraintsansi-sql-92

CHECK constraint evaluation in SQL 92

when is check constraint evaluated as per SQL 92 standards?

create table a (
   val INT
);

create table b (
   f CHECK ( f in (SELECT val from a))
);

a) Is CHECK with sub-query allowed as per SQL-92 standards?

b) If yes, When is CHECK executed?

scenario:
insert 1 into a
insert 1 into b
delete 1 from a (CHECK is violated here, but is it checked again?)
Solution

  • The answer is no.

    (optional) Feature F671, ‘‘Subqueries in CHECK constraints’’ was introduced in SQL-99.

    (However, I'm not sure which table(s) was allowed to reference.)