Subject Re: [Firebird-Architect] Nulls in CHECK Constraints
Author Dmitry Yemanov
"Martijn Tonies" <m.tonies@...> wrote:
> One other thing about our Check Constraints:
> They allow access to other tables.
> This is prohibited in the SQL (2003) standard

Do you have a page reference inside SQL-2003 which explicitly states this

This is what I see, for example:

a) If T is a persistent base table, or if the <check constraint definition>
is contained in a <domain definition>
or <alter domain statement>, then no <table reference> generally contained
in the <search condition>
shall reference a temporary table.

For me, it implicitly means that <table reference>s to persistent tables are
allowed. Also, feature F671 explicitly allows subqueries in the <search
condition>. Do you think they're limited to the host table only? I doubt

Our inconsistency is that a check constraint may select from stored
procedures that modify data. This clearly violates the standard. As for
check constraints being multi-table, I'm not sure this is aganst the spec.

> as a check constraint should be a row constraint.

A referential integrity constraint is also a row one, but it does reference
another table.

> I guess this is an implementation error

Some our users would disagree :-)

> given that Firebird check
> constraints aren't evaluated completely if, for example, you delete
> a row in the table.

IMO, this is another issue.

> The SQL standard says, I believe, that there's an "assertion" object
> which can be used for table or database (multi-table) constraint.

Correct. Although I don't see how this restricts check constraints from
referencing other tables.