Hello Dmitry,

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

Should have said: from what I understood, this is prohibited in the
SQL 2003 standard. (more comments below)

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

Might be, but given that our CHECKs are only evaluated on insert/update
(and not even on DELETE), could make them useless in certain occasions
when referring to other tables.

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

See above, fine with me if it's possible, but then I expect them to work :-)

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com