CHECK constraint evaluation in SQL 92

Question

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?)

Regardless of what the standard says, you'll have a very hard time finding an engine that allows this, precisely because of the difficulties in determining when the constraint should be checked (doing so on every statement is possible in principle, but so grossly inefficient that no practical engine could afford that). What you've written here might as well be done with a plain old foreign key, which is much more straightforward to check. — Jeroen Mostert, Dec 21 '22 at 20:50
@JeroenMostert thanks for the comment! It was a theoretical question in one of the tests. I think the question is stupid, but just to be sure I asked here! — Khushit Shah, Dec 21 '22 at 21:14

jarlh · Accepted Answer · 2022-12-21T21:00:32.883

1

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.)

edited Dec 21 '22 at 21:00

answered Dec 21 '22 at 20:48

jarlh

1 Answers1