Not sure I understand your question, but I'll give it a try anyhow. CHECK constraints containing SELECT is valid according to standard (see F671 in for example http://jtc1sc32.org/doc/N1201-1250/32N1208-TC9075-P02-foundation.pdf), but very few DBMS:s supports this. Firebird claims to have this feature
http://www.firebirdsql.org/en/sql-conformance/.
SQL-server does not directly support it, but you can hide the select in a function, and use the function in the constraint.
Other than that, I don't know.
As others have pointed out in this thread the construction is problematic from an implementation perspective, but it is not without merits. "Sub-typing" in RDBM:s is often implemented as:
CREATE TABLE Parent
( key ... not null primary key
, type ... not null
, constraint ak1 unique (key, type)
, check (type in ...));
CREATE TABLE child1
( key not null primary key
, type ... not null
, constraint fk1 foreign key (key, type) references parent (key, type)
, constraint c1 check (type = ...));
ak1 ^ fk1 ^ c1 is a type control that guarantees that parent.type is consistent with any rows inserted into child1. It can however be argued that having to declare the type in child1 is unnecesary and redundant, as is having to declare the redundant unique constraint ak1. Allowing sub-selects in a constraint allows for a more elegant solution:
CREATE TABLE Parent
( key ... not null primary key
, type ... not null
, check (type in ...));
CREATE TABLE child1
( key not null primary key
, constraint fk1 foreign key (key) references parent (key, type)
, constraint c1 check
((select type from parent p where p.key = key...) = ...);
No need for type in child1 and a redundant ak1 key in parent. From a theoretical relational point of view, ak1 is reducable (it is a superkey of the primary key), and therefore questionable as an alternative key.