Re: [Gnumed-devel] Might ref.consumable substances need to allow an amount of zero?

[Karsten Hilbert]

On Sat, Sep 03, 2011 at 08:52:41PM -0700, Jim Busser wrote:

> Some allergy testing (diagnostic) preparations are rated
> as having a strength of 0 units per mL. This makes sense
> when it would be the "base" against which other "live"
> products are tested.

Does not seem sane scientifically, but makes sense
clinically.

> 1. shall the constraint be dropped from v16,

No, I modified it to:

        alter table ref.consumable_substance
                add constraint ref_consumable_sane_amount
                        check (amount >= 0);

> 2. in terms of how (technically) to drop temporarily, I imagine I can borrow 
> from
> 
>       
> [...]/git/gnumed/gnumed/gnumed/server/sql/v14-v15/dynamic/v15-ref-consumable_substance-dynamic.sql
> 
> alter table ref.consumable_substance drop constraint 
> ref_consumable_sane_amount cascade;

Technically, yes.

> -- and, when finished:
> 
> alter table ref.consumable_substance
>       add constraint ref_consumable_sane_amount
>               check (amount > 0);

You cannot re-add it afterwards if your data now contains
0-amounts. That's the whole point. It 0 is legal or it is
not.

> 3. I am still learning about cascade … I was
> understanding it to be the action desired when a foreign key
> was attempted to be deleted, therefore can I be helped to
> understand its role above?

Well, "cascade" means: "propagate appropriate action to
dependant objects". In this case it will propagate the
dropping of the constraint to, say, a unique index that
depends on the constraint.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346