[zasdffaa]

Jesus. I feel there may be a touch of dunning-kruger about you. From that book (page xvi)

" Standard SQL is relationally complete in its support for declarative constraints by permitting the inclusion of query expressions in the CHECK clause of a constraint declaration.

We do not yet know—and it is an important and interesting research topic—how to do the kind of optimization that would be needed for the DBMS to work out efficient evaluation strategies along the lines of the authors’ custom-written solutions. "

So it can't. But then you say.

> But the reasons are ENTIRELY related to SQL's depending on 3VL.

Easy to fix. Simply require the assertion to be defined on tables (ok, ok, relvars) with 'not null' on every column, also require a PK to ensure uniqueness, and you're away. Except you aren't.

I'm not going to argue with you. Your knowledge is clearly considerable but it comes with something extra I don't need.

[ErwinSmout]

The basic text of the book was written before Lex died (must have been +- 2006), my work in which I cracked that problem saw the light of day after that time. So "we do not yet know ..." was technically correct at the time they wrote it, it no longer would be now.

That other snippet "Standard SQL is relationally complete ... CHECK clause ..." is technically correct, but the standard allows subqueries referencing other tables than the one the CHECK clause is on, but as far as I'm aware no product supports that (and the ones that do leave the user exposed to risk of faulty behaviour). Sadly, such a feature is necessary if we'd want to write, say, an FK constraint in the form of CHECK clause on the referencing table : CHECK (EXISTS (SELECT 1 FROM PARENT WHERE <FK equality tests here> ) ).

[ErwinSmout]

With one single exception : you cannot use a CHECK clause to check that a table must be nonempty, because the semantics of the CHECK clause are that it must be satisfied by all rows in the table, and therefore the empty table trivially satisfies any CHECK clause.

[zasdffaa]

Huh! That makes sense, thanks! It's standard univ. quantifier behaviour, I hadn't made the link.

Also that's bloody odd because date or darwen (or both) explicitly called out this behaviour as flawed in SQL when in fact it's obviously logically consistent, and they know logic and SQL, so... why are they critising it? How very strange.

Again, thanks for pointing this out. How did I never realise this?

[ErwinSmout]

What they "called out as flawed" was the impossibility to declare any such rule with SQL as it stood. What they were "criticizing" was precisely the fact that a data language that was supposed to be "expressively complete" was *unable* to express such a rule. You can't claim "expressive completeness" for a language if there is demonstrably a case where said language's expressiveness fails to meet the mark. It's not the "behaviour of the language" they were criticizing in all/any of those use cases that were supported by the language as it stood. They were criticizing the fact that there was a use case [and a relatively reasonable one on the face of it] that the language couldn't support.