Re: foreign key references two different table?

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
Received on Fri May 30 2003 - 17:55:18 CDT