Re: foreign key references on view

Sybrand Bakker wrote:
>
> On Tue, 26 Mar 2002 14:20:21 +0100, zorg <czorman_at_wanadoo.fr> wrote:
>
> >this a simple of the problem
> >i've two table which share the same key "numcom"
> >
> >
> >
> >CREATE individual(
> > numcom NUMBER(4) primary key,
> > name VARCHAR(30),
> > );
> >
> >
> >CREATE group(
> > numcom NUMBER(4) primary key,
> > responsable VARCHAR(30),
> > );
> >
> >
> >i've another table (like reservation) which has got a foreign key that
> >should be the union of the field numcom
> >
> >1/ should i create a view like
> >create view allkey
> >select numcon from individual
> >union
> >select numcon from group(
> >
> >and
> >add constraint like
> > foreign key (the_other_table) references allkey(numcon)
> >don't know if it work and if it's the best way to do it
> >
> >hope the problem is clear
> >
> >thank
>
> Foreign keys to views are NOT possible.
> A more or less straightforward solution looks like
> merge the two tables into one, adding an extra column, which will
> contain a switch indicating which type of record you have.
> Define views referring to the value of the switch for each subtype you
> have in your table.
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

I haven't checked them out, but rumour has it that you can add constraints to view in 9i (but I think they have to be DISABLED NOVALIDATE) hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."