Re: ForeignKey-Constraint with more than 1 tables

Andreas Mosmann a écrit :
> Hi ng,
>
> (Oracle 9.2.0.7i)
>
> I talk about a database concerning real Trees.
> There is a table TTrees.
>
> A single Tree belongs either to a forrest or to a street or to a park or
> whatelse.
>
> So there exist f.e. a table TStreets and a table TForrests.
> in the table TTrees actually exist a field for each Table, f.e.
>
> CIDFORREST
> CIDSTREET
>
> Exactly 1 of them is not NULL, all the others are definitely NULL.
>
> Now my Questions:
> Is there a way to substitute this by only 1 Field, f.e. CID_BELONGS_TO
> _AND_ to build a FK-constraint like
>
> TForrests.CID=TTrees.CID_BELONGS_TO OR
> TStreets.CID=TTrees.CID_BELONGS_TO ...
> ?
>
> Is there a possibility to build a constraint, that observes that _exact_
> 1 of the fields (CIDFORREST, CIDSTREET ..) is not null? (I know, I could
> use triggers for it)?
>
> Is there a better way to build this database? (The Columns of TTrees are
> 95% equal, no matter if it stands on a street or in a forrest, so there
> is only 1 table)
>
> Many thanks
> Andreas Mosmann

With Oracle 9i, this can't be performed without major overhead AFAIK. With 11g, you could make use of the new virtual column functionality, e.g. :

SQL> select * from forrests;

        ID FNAME
---------- ----------

         1 Blah f

SQL> select * from streets;

        ID SNAME
---------- ----------

         2 Foo s

SQL> create table trees(id int,place_type char(1),cid_place int,cid_forrests as (case when place_type='F' then cid_place else null end) references forrests,cid_streets as (case when place_type='S' then cid_place else null end) references streets);

Table crÚÚe.

SQL> insert into trees(id,place_type,cid_place) values(100,'F',1);

1 ligne crÚÚe.

SQL> insert into trees(id,place_type,cid_place) values(200,'F',2); insert into trees(id,place_type,cid_place) values(200,'F',2) *
ERREUR Ó la ligne 1 :
ORA-02291: violation de contrainte d'intÚgritÚ (JEROME.SYS_C009673) - clÚ
parent introuvable

SQL> insert into trees(id,place_type,cid_place) values(200,'S',2);

1 ligne crÚÚe.

SQL> insert into trees(id,place_type,cid_place) values(300,'S',3); insert into trees(id,place_type,cid_place) values(300,'S',3) *
ERREUR Ó la ligne 1 :
ORA-02291: violation de contrainte d'intÚgritÚ (JEROME.SYS_C009674) - clÚ
parent introuvable

SQL> select * from trees;

        ID P CID_PLACE CID_FORRESTS CID_STREETS ---------- - ---------- ------------ -----------

       100 F          1            1
       200 S          2                        2

SQL> Received on Fri Mar 28 2008 - 14:17:01 CDT