Re: Separate foreign keys with shared ID space

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 28 Jul 2004 06:34:19 -0700
Message-ID: <6dae7e65.0407280534.75936feb@posting.google.com>

robertbrown1971_at_yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407271513.43dfecc7_at_posting.google.com>...
> Let's say I have a type hierarchy: (just an example)
>
> the general entity customer:
>
> CREATE TABLE customer(customer_id int, customer_name varchar(250),
> customer_type int)
>
> three specific customer subtypes: 1 - business, 2 - home, 3 -
> university
>
> represented by the following three tables (being subtypes, they share
> ID space for customer_id)
>
> CREATE TABLE business_customer(customer_id int, business_sector int,
> ... )
> CREATE TABLE home_customer(customer_id int, household_income_bracket
> int, ...)
> CREATE TABLE university_customer(customer_id int, number_students int,
> ...)
>
> 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?
>
>
> Thanks!
>
> - Robert

Something like:

CREATE TABLE customer (

    customer_id int not null primary key,
    customer_name varchar(250) not null,
    customer_type int not null check (customer_type in (1,2,3)),

    unique (customer_id, customer_type ) );

CREATE TABLE business_customer (

    customer_id int not null primary key,     customer_type int not null default 1 check (customer_type = 1),     ...
    foreign key (customer_id, customer_type)

        references customer (customer_id, customer_type) );

etc.

HTH
/Lennart Received on Wed Jul 28 2004 - 08:34:19 CDT