It seems like type hierarchies are a common technique in relation
design. What approaches do people use to provide relational integrity
in cases like this? Should I add the customer_type column to the
subtypes and then use composite foreign key? I guess that could work
but it has the downside of using up tablespace with a column of
customer_type that will always have the same value for the subtype. Is
that considered just the cost of doing business in this situation?
"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:<410722dd$1_at_post.usenet.com>...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi Robert
>
> > Is it possible to make a foreing key constraint that says:
> >
> > the customer_id in busineness_customer table must reference only those
> > customer_id in the customer table where customer_type == 1?
> >
> > the customer_id in home_customer table must reference only those
> > customer_id in the customer table where customer_type == 2?
>
> With a foreign key you can only reference a primary key. Since customer_type
> is not part of it, you cannot use it on the subtypes.
>
> On the other side, if you add customer_type to the customer's primary key,
> you should add it on the subtype as well. But the foreign key alone will not
> be enough, i.e. you should add a check constraint on the subtype to ensure
> such a rule.
>
> ==> There is no good (from a design point of view) foreign key to enforce
> such a constraint.
>
>
> Chris
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Received on Wed Jul 28 2004 - 08:57:58 CDT
