Re: ForeignKey-Constraint with more than 1 tables

Andreas Mosmann 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
>
>
>
>

The "standard" solution would be to introduce a type column in your trees table. That would indicate what type the relation is; park, street, forest or whatelse. Giving 4 options, a CHAR(1) NOT NULL column would suffice.
A simple check constraint (col value in ('P','F','W','S')) would cover that.

You will probably need technical foreign key columns, if not all primary keys of your referencing tables (parks, streets, forests, whatevers) are the same. If they are, or you use technical keys (number(32)), you might get away with a single reference_id column in table trees. I would rather go for the truly FK option: define 4 FK columns: park_id, forest_id, street_id and whatever_id. You can then define proper foreign key references within Oracle - the first option would require kludges with triggers, and that will never perform like the FK stuff with Oracle itself.

An after insert row trigger can check if one of the fields is filled in - the columns themselves should be NULLable!

Of course, there are other ways of doing this, e.g. with an extra intersection table per reference table. This allows for streets though parks as well :) (Not only "Any tree must belong to a street OR a park OR a whatever OR a forest" but also "Any tree may belong to a street AND a park")

-- 

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up