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 logical. 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 http://www.postgresql.org/docs/8.0/static/sql-createdomain.html Jochem
