Re: ForeignKey-Constraint with more than 1 tables


"Frank van Bortel" <frank.van.bortel@xxxxxxxxx> schreef in bericht
news:baf50$47ed42e1$524b5c40$3239@xxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: ForeignKey-Constraint with more than 1 tables
    ... 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 ...
    (comp.databases.oracle.misc)
  • Re: ForeignKey-Constraint with more than 1 tables
    ... I talk about a database concerning real Trees. ... There is a table TTrees. ... 95% equal, no matter if it stands on a street or in a forrest, so there ... ERREUR Ó la ligne 1: ...
    (comp.databases.oracle.misc)
  • Re: ForeignKey-Constraint with more than 1 tables
    ... I talk about a database concerning real Trees. ... There is a table TTrees. ... Normally one would model a supertype for locations, keeping the problem away from the trees table or other possible referncing tables. ... Every sub type its own table (explicit subtyping); ...
    (comp.databases.oracle.misc)
  • Re: ForeignKey-Constraint with more than 1 tables
    ... 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. ... a whatever OR a forest" but also "Any tree may belong to ...
    (comp.databases.oracle.misc)
  • Re: ForeignKey-Constraint with more than 1 tables
    ... I talk about a database concerning real Trees. ... There is a table TTrees. ... One of the standard options is to create a 'supertype' for STREETS, FORREST, ... number of possible location types is not dictating the number of foreign key ...
    (comp.databases.oracle.misc)