Re: ForeignKey-Constraint with more than 1 tables

On Mar 28, 5:04 am, Andreas Mosmann <mosm..._at_expires-31-03-2008.news- group.org> wrote:
> 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
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

I am curious. What advantage do you see in this single column solution? What data model rule are you trying to enforce here?

I see disadvantages. e.g. find all trees that belong to forests now requires a table join where the two column model did not.

If you are truly focussed on this solution, a trigger can enforce the rule.

   Ed Received on Fri Mar 28 2008 - 09:00:12 CDT