Re: ForeignKey-Constraint with more than 1 tables

From: Shakespeare <>
Date: Sat, 29 Mar 2008 12:57:13 +0100
Message-ID: <47ee2ea0$0$14357$>

"Frank van Bortel" <> schreef in bericht news:baf50$47ed42e1$524b5c40$
> Andreas Mosmann wrote:
>> Hi ng,
>> (Oracle
>> 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.
>> 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
>> 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.

I think you should find the solution where the problem is: it is not in the trees table, but in the location types. Normally one would model a supertype for locations, keeping the problem away from the trees table or other possible referncing tables.



> Regards,
> Frank van Bortel
> Top-posting in UseNet newsgroups is one way to shut me up
Received on Sat Mar 29 2008 - 06:57:13 CDT

Original text of this message