Re: ForeignKey-Constraint with more than 1 tables
Date: Fri, 28 Mar 2008 07:00:12 -0700 (PDT)
On Mar 28, 5:04 am, Andreas Mosmann <mosm..._at_expires-31-03-2008.news- group.org> wrote:
> Hi ng,
> (Oracle 18.104.22.168i)
> 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
> 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.
> 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