Re: Conditional Relationships ?

From: Henry Craven <GospodynNiemandt_at_nyet.net>
Date: Sun, 29 Dec 2002 13:36:02 GMT
Message-ID: <6TCP9.6493$aV5.18214_at_news-server.bigpond.net.au>


But in that case surely you would store the data in the one table with a boolean
indicator for foreign or domestic (?) and have just the One FK (?)

In my case the business rules are such thet Invoices based on Orders have a different value to those based on Order_Items which one might expect even in the simpleat scenarios as freight and handling contribute to differing total values
but in some cases "Quoted" / Order price would vary from std orders.

However, all that aside:

My thanks for the reference to CREATE ASSERTION I'd never heard of it and will follow it up.

easiest solution though would appear to be Erland Sommarskog's Table level constraint on the FK's while allowing Nulls.

"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."

Or am I missing something here ???

Many thanks for your time.
H.

"--CELKO--" <71062.1056_at_compuserve.com> wrote in message news:c0d87ec0.0212282111.35e1b34e_at_posting.google.com...
> >> I'm curious as to how one would go about setting up and enforcing a
> Conditional Relationship. <<
>
> Try a better example; a student is either a foreign students or a
> domestic student. Unlike your orders and order details example, the
> two cases are clearly not related to each other.
>
> In Standard SQL-92, you would use a CREATE ASSERTION statement, which
> is like CHECK() constraint at the schema level. Here is one way
>
> CREATE ASSERTION StudentClassification
> CHECK (((SELECT student_nbr FROM DomesticStudents
> UNION ALL
> SELECT student_nbr FROM ForeignStudents)
> EXCEPT
> SELECT student_nbr FROM Students) IS NULL);

---
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: 18-Dec-2002
Received on Sun Dec 29 2002 - 14:36:02 CET

Original text of this message