Re: ForeignKey-Constraint with more than 1 tables

"Frank van Bortel" <frank.van.bortel_at_gmail.com> schreef in bericht news:baf50$47ed42e1$524b5c40$3239_at_cache6.tilbu1.nb.home.nl...
> 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.
>

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.

<Snip>

-- 


>

> Regards,

> Frank van Bortel

>

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



Shakespeare