Re: foreign key references two different table?

Mikito Harakiri wrote:

> create table a1 (
> id integer
> )
>
> create table a2 (
> id integer
> )
>
> create view a as
> select id from a1
> union all
> select id from a2
>
> ALTER VIEW a ADD (CONSTRAINT a_pk
> PRIMARY KEY (id) DISABLE NOVALIDATE);
>
> create table b (
> id integer
> )
>
> ALTER TABLE b
> ADD CONSTRAINT fk_id FOREIGN KEY (id)
> REFERENCES a (id) ON DELETE CASCADE DISABLE NOVALIDATE
>
> If id is nonunique key in view "a", then make it a composite key by adding
> type column.
>
> Unfortunately, you can't enable those constraints:-(
>
> "Susan Lam" <susana73_at_hotmail.com> wrote in message
> news:7186ed56.0305301413.50ce90f7_at_posting.google.com...
>
> > I have 3 tables.
> >
> > parent1 (id, ...)
> > parent2 (id, ...)
> > child (id, memberid, type)
> >
> > child.memberid came from parent1.id and parent2.id. child.type
> > distinguishs where the memberid are from parent1 or parent2 table.
> >
> > I would like to have a fk relationship between child.memberid ->
> > parent1.id and parent2.id. However, I can't create two separate fks
> > because parent1.id and parent2.id together are not unique.
> >
> > Is there a way to create one fk to two different tables?
> >
> > Thanks.
> > Susan

[Quoted] Well if you want to bring a view into it the solution could be written using [Quoted] an INSTEAD OF trigger.

[Quoted] But it is still a bad design.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)