List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:February 22 2005 6:50pm
Subject:Re: MySQL constraint question
View as plain text  
> > I have a database thats full of ingredients that are placed in various
> > categories.  and then there are mixtures that are allowed to
> > be labelled with a certain grade based on the quality and
> > composition of the ingredients it is comprised from.
> > But the formulas are not always the same, as long as the profile matches
>
> > certain criterian.
> >
> > For example, in order to be label grade Premium,
> > it must have between 70% and 95% ingredients from group A
> > between 0 and 15 % from group B
> > between 5 and 15% from group C
> > between 0 and 15% from group D
> >
> >
> > What you actually put in the mix is determined by price
> > and availability and other factors.
> >
> > So I  implement these profiles with a mySQL table
> > I have
> >
> > grade_id               points to main grade record
> > category_id            points to category
> > min_percent
> > max_percent
> >
> > and the individual ingrediants
> >
> > ing_id
> > name
> > price
> > category_id    among others...
> >
> > The question is
> > Is there some way mysql can assure the integrity of these profiles, so
> > that there is no way to use a set of records for a grade that can't add
> > upto 100%.     Or is this pretty much application logic?
> >
> >
> >
>
> Nope, the "combined total < 100%" calculation will have to be
> application-based as it requires validating an inserted or updated row
> based on the value(s) contained in other row(s). CHECK constraints, which
> are in the development pipeline, can only be used to validate a row
> against constant values or some combination of values from within the row
> being evaluated. Any time you need to compare a group of rows in order to
> validate the group, you have left the automation of SQL logic and are in
> the realm of application logic, as you guessed.

Disagreed :-)

A decent database system would be able to create multi-row check
constraints -> there are several types of constraints.

1) column constraints
2) table constraints
3) database/schema constraints

These would fall under (3) I guess.

Firebird allows queries in its check constraints, but only enforces
the constraints at INSERT or UPDATE time.

A database system that would support "deferred" constraints should
be able to create multi-table, multi-row check constraints just fine.

However, I don't know any DBMS that currently does that. Perhaps
Mimer or ThinkSQL...

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

Thread
MySQL 4.1.9 - Requesting Optimization TipsMatt Florido18 Feb
  • Re: MySQL 4.1.9 - Requesting Optimization TipsPaul DuBois18 Feb
    • Re: MySQL 4.1.9 - Requesting Optimization TipsMatt Florido18 Feb
      • Re: MySQL 4.1.9 - Requesting Optimization TipsPaul DuBois18 Feb
      • MySQL constraint questionGerald Taylor18 Feb
        • Re: MySQL constraint questionSGreen22 Feb
          • Re: MySQL constraint questionMartijn Tonies22 Feb
            • Re: MySQL constraint questionGerald Taylor23 Feb