List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 20 2005 7:38am
Subject:Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
View as plain text  
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

Thread
Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten19 Oct
      • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies20 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten20 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies20 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesHarald Fuchs20 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten20 Oct