Re: Foreign key to multiple tables ???

From: Kirmo Uusitalo <kirmo.uusitalo_at_comptel.fi>
Date: 1996/12/03
Message-ID: <580l4s$fk0@pinta.kolumbus.fi>#1/1

In article <57upq0$j6q_at_speedy.daugherty.com>,

   thomasf_at_daugherty.com (Frank Thomas) wrote:

>One option would be to create a generic account_types table that uses
>the acount_class and account_type fields as key. You could then
>directly link the customer_account table to the account_types table
>using account_class and account_type as foreign keys.

Yes, but there are times when the different types of classes have "nothing in common", resulting in huge (multi-column) generic table with no analogue to real world.

What I was (and still am) looking for is an method to explicitly declare data integrity to multiple tables.

How about the following extension?

create table customer_account (customer_id number(9),

                              account_class varchar2(10) NOT NULL,
                              account_type number(2) NOT NULL,
     constraint account_class_ok check (account_class in ('SAVINGS','CHECK')),
     constraint account_type_fk foreign key (account_type) 
        refererences      savings_account_types (account_type) 
           when account_type='SAVINGS' and
        references check_account_types (account_type) 
           when account_type='CHECK');

create table savings_account_types(account_type number(2) primary key,
                                   ...)

create table check_account_types (account_type number(2) primary key,
                                   ...)

N.Prabhakar Wrote:

>>If you need complete data integrity, then have 2 columns in child table.
>>Only one can have a value at any time.

This approach has some limitations: you would have to have extra columns in the table, especially when the number of referenced tables is high. You would also have to add value-checking against update to force excactly one of the fk columns being not null.

Thanks to all who responded,

Kirmo.Uusitalo_at_comptel.fi               Comptel Oy
System Analyst                          Ruoholahdenkatu 4
tel. +358-9-70011662                    00180  HELSINKI
fax         70011385                    FINLAND

Received on Tue Dec 03 1996 - 00:00:00 CST