# Re: ForeignKey-Constraint with more than 1 tables

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 29 Mar 2008 12:57:13 +0100
Message-ID: <47ee2ea0\$0\$14357\$e4fe514c@news.xs4all.nl>

"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
```
Received on Sat Mar 29 2008 - 06:57:13 CDT

Original text of this message