Re: Constraints with several father tables

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 24 Jul 2003 14:13:16 -0700
Message-ID: <a20d28ee.0307240542.3baa5d3d_at_posting.google.com>


Miko_at_boehrer.de (Ralf) wrote in message news:<fa624200.0307240031.20278673_at_posting.google.com>...
> Hi,
>
> I have 4 tables: "T_father1",...,"T_father4".
> In relation to these father-tables I have a child-table: "T_Child"
> (with only 1 foreignkey-field: FsID) alread filled with some records.
>
> That means each record of "T_Child" has in its field "FsID" a primary
> key of one of the 4 father tables.
>
> If I want to create the 4 foreign-key CONSTRAINTS:
> "T_Child.FsID <-> T_FatherX.ID" (X=1..4)
> I get an Oracle-error (Ora-02298): "cannot validate".
>
> My question is:
> How can I tell Oracle (how do I have to create the Constraint), that
> this foreignkey-field "T_Child.FsID" is not the foreign-key to ONE
> father-table but to FOUR father tables ?
> Or is this "bad database design" to have one foreignkey-field for 4
> father tables ?
>
> Thanks in advance for any help, suggestionst or comments !!
> Best regards
> Ralf Miko

I wouldn't even call this design. Multiple foreign keys in multiple tables to 1 PK are definitely possible. One foreign key to multiple parents of course not.
Your parent table must be falling apart in several subtypes. Hence you need to differentiate.

Sybrand Bakker
Senior Oracle DBA Received on Thu Jul 24 2003 - 23:13:16 CEST

Original text of this message