Re: Separate foreign keys with shared ID space
Date: Wed, 28 Jul 2004 05:51:53 +0200
Message-ID: <410722dd$1_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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=