Re: Foreign key to multiple tables ???

N Prabhakar <prabhs_at_po.pacific.net.sg> 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.

>kirmo.uusitalo_at_comptel.fi (Kirmo Uusitalo) wrote:
>>Hi,
>>
>>Suppose I want to create three tables: one "child" table and two master
>>tables:
>>
>>
>>create table customer_account (customer_id number(9),
>> account_class varchar2(10),
>> account_type number(2),
>> ...)
>>
>>create table savings_account_types(account_type number(2),
>> ...)
>>
>>create table check_account_types (account_type number(2),
>> ...)
>>
>>In the child table customer_account field account_class implies whether
>>account_type refers to savings_account_types (account_class='savings') or
>>check_account_types (account_class='check').
>>
>Hi there,

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

>If you implement this approach, you can create FOREIGN key pointing 1st
>column to MASTER TABLE 1 and 2nd column to MASTER TABLE 2. There will be
>no compromise on data integrity (except NULL values).
 

>If the child table does not hold huge records or if the column which you
>need to store is small (in terms of data length) then this option is
>viable.

>Regards
 

>N.Prabhakar

Frank Thomas
thomasf_at_daugherty.com Received on Mon Dec 02 1996 - 00:00:00 CST