Hello Jochem, > >> 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.
Thanks for setting that straight. Given that both InterBase and Firebird allow you to use other tables in their CHECK constraints seems as a flaw then and I regarded them as both row and table constraints. Or as a non-finished implementation of a "assertion" like behaviour from the era before the (latest?) SQL standard. :-) > 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? Depending on the implementation (eg: the assertion should check what columns and rows involved and do a useful check there), I don't see a problem. > This is something you enforce with triggers or > circumvent by using a stored procedure for all DML operations. The beauty of constraints is that it will even work without procedures :-) But I guess we're on the same level here. > 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 The DOMAIN, love it ;-) ... I use them heavily in my InterBase and Firebird applications :-) 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
