Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Conditional Relationships ?

Re: Conditional Relationships ?

From: Henry Craven <GospodynNiemandt_at_nyet.net>
Date: Wed, 01 Jan 2003 02:44:15 GMT
Message-ID: <3CsQ9.17726$aV5.45892@news-server.bigpond.net.au>


perhaps I should have Posted Erland's solution in full.

Quote:



If there are two different columns for Order and Order_Item, then this table-level constraint would do the trick:

   CONSTRAINT ckt_invint_orderref CHECK (

       Order IS NULL AND Order_item IS NOT NULL OR
       Order IS NOT NULL AND Order_item IS NULL)

And the regular foreign-key constraints to the Orders and Order_items table.


While at i'ts simplest this does not appear to prevent separate Invoices being raised for the Order alone , and then for Order items of that order if you use an Order_ID and the Order_Items_ID - as you point out - , the method can be applied by using additional Checks

So:
CONSTRAINT ckt_invint_orderref CHECK (

( Invoice.Order_ID IS NOT NULL
AND
  Invoice.Order_Items_ID IS NULL
AND
  Invoice.Order_ID NOT IN
 ( SELECT invoice.order_id
   FROM invoice
   WHERE invoice.cancelled = 0 ))

 OR

( Invoice.Order_ID IS NOT NULL
AND
  Invoice.Order_Items_ID IS NOT NULL
AND
  Invoice.Order_ID NOT IN
 ( SELECT invoice.order_id
   FROM invoice
   WHERE invoice.cancelled = 0

      AND invoice.order_items_id IS NULL ))

)

Note: this is Air-Code and I haven't checked this yet.

H.

"Neil Burnett" <neil_at_efc.be> wrote in message news:ea0243b5.0212310740.20f1e76d_at_posting.google.com...
> I can't see how this solves your problem. It is still possible to have
> an invoice for a full order existing as well as an invoice for order
> items off that order:

>

> Invoice Order OrderItem
> ------------------------
> a1 o1 null
> a2 null oi1
>

> OrderItem Order
> ---------------
> oi1 o1
>

> I am not clever enough to offer a solution though:-((
>
> "Henry Craven" <GospodynNiemandt_at_nyet.net> wrote in message
news:<qgKP9.8072$aV5.20299_at_news-server.bigpond.net.au>...
> > Thanks for the response Steve.
> >
> > The "Real World" reality of the business rules are a little more
complicated
> > and simplest to say that the invoice totals vary depending upon whether
> > they are based on the Order, or sum of the Order_Items. Values.
> >
> > Needless to say, I have no chance of changing the corporate Method
> > to suite my "Ideal" data model.
> >
> > Erland Sommarskog in comp.databases.ms-sqlserver
> >
> > seems to hav hit on the simplest solution Allow nulls on the FKs
> > and appky a conditional ( either / or ) Rule
> >
> > Thanks again
> > H.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.431 / Virus Database: 242 - Release Date: 17-Dec-2002
Received on Tue Dec 31 2002 - 20:44:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US