List:General Discussion« Previous MessageNext Message »
From:Gerald Taylor Date:February 23 2005 3:36pm
Subject:Re: MySQL constraint question
View as plain text  
Thanks for these answers.  I can now write this in the application and
be OK with it without that nagging feeling that somebody will say upa
if you just put xxx in the database then you could filter all that in 
your query.  If it ever happens I can say, ah yes but when I wrote that
we were only on version 4.

When you have a problem set that pushes the envelope of what it
can do... that's how people get the ideas to make it do a new level of 
things in the future, such as you are envisioning.

Martijn Tonies wrote:
>>>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