Re: Multi-table CHECK constraint

On Wed, Dec 10, 2008 at 05:58:08PM -0600, Jason Long wrote:
> I need to add some complex constraints at the DB.
>
> For example.
>
> Do not allow a line item of inventory to be changed if it does not
> result in the same number of joints originally shipped.
>
> These will involve several tables.
>
> What is the best approach for this?

Triggers.

> Here is what I have been trying.
>
> CREATE OR REPLACE FUNCTION numoriginaljts(genericitem_id bigint)
> RETURNS double precision AS
> 'select coalesce(vsjo.diff,0) from inventory.t_generic_item gi
> left join view.generic_item_shipment_id v on v.id=gi.id
> left join v_shipment_jts_off vsjo on vsjo."shipmentId"=v.shipment_id
> where gi.id=$1;'
> LANGUAGE 'sql' VOLATILE
> COST 100;
> ALTER FUNCTION numoriginaljts(bigint) OWNER TO exploreco;
>
> alter table inventory.t_generic_item add constraint
> check_shipment_original_jts CHECK (numoriginaljts(id)=0);
>
> *Does this approach seem reasonable?

Nope. You're lying to the database by wrapping otherwise disallowed
SQL in a check constraint, and it will get its revenge.

> This did not work, but it is probably my error. It actually let me
> break the constraint, but my constraint kicked in when I tried to
> correct the problem. Can someone point me to an example of doing
> something like this?*
>
> The point of this is to never let the total number of original
> pieces be different than the number originally shipped.
>
> My code has done this occasionally and users can override the
> inventory.
>
> Basically I would rather the application throw an error than let
> this number become unbalanced.

You might want to talk to people who have done bookkeeping
applications for PostgreSQL, or possibly even buy one of the
proprietary PostgreSQL-based systems for it, as this stuff can be
fiendishly tricky to get right.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate