On 10/20/05, Martijn Tonies wrote:
>> That doesn't help: check constraints are evaluated only on insert and
>> update, not on delete. That's why you need an assertion.
> Hmmm, would that be SQL standard? Or implementation specific?
It is at the very least implied in the SQL standard.
> From a logical point of view, I don't see why a "check constraint"
> shouldn't be checking on all operations.
A CHECK is a row constraint. If there is no row, there is no
constraint. IIRC a CHECK constraint is only allowed to refer to the
row itself in the SQL standard, so that behaviour is perfectly
An ASSERTION is a table constraint that is the equivalent of a CHECK
constraint for a row. If you wanted to maintain some sort of
constraint that for every distinct value in some column there are at
least X and at most Y occurences, an ASSERTION is the way to go. An
ASSERTION is a table constraint so it exists as long as the table
exists, even when there are no rows in the table.
Back in reality you don't enforce this using DDL. Apart from the fact
that I wouldn't know a single database that implements ASSERTIONs
according to the SQL standard, can you imagine having to run some
SELECT fk FROM table GROUP BY fk HAVING COUNT(fk) NOT BETWEEN x AND y
on every commit? This is something you enforce with triggers or
circumvent by using a stored procedure for all DML operations. Just
take care you use a serializable transaction if your database is more
concurrent then is good for you.
BTW, with regard to the original question of how good different
RDBMS's are in enforcing data integrity: apart from Access pretty much
anything is better then MySQL. Even if we assume that you run MySQL 5
in strict mode, the absence of CHECK constraints is a huge problem.
My personal preference would be PostgreSQL. And the best kept secret
of data integrity is