Re: Separate foreign keys with shared ID space

• 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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=