Thread: Foreign Keys and Inheritance

If I have table A, which contains a primary key,
and table B which inherits from A.

How can I Create a Foreign Key on table C that references A, but will
also pass if a record in inserted in to B.

for instance:

create table A
(
        prim_key char(20) not null primary key
);

create table B
(
) INHERITS A;


create table C
(
        data char(2) not null primary key,
        constraint fk_C FOREIGN KEY ( data ) REFERENCES A ( prim_key ) ON DELETE
CASCADE ON UPDATE CASCADE
)



With this setup, my record must be inserted in to A or I'll get a
referential integrity problem when I insert in to C.

I want to be able to insert in to A, B , or another table inherited from A,
and have table C recognize that as it's foreign key.

Thanks

Currently you can't do that.  Foreign keys are only referencing the
explicitly named tables.  This will probably change around the time
that someone goes in and does alot of work on inheritance.  BTW:
primary keys (and unique) also do not inherit.

On Fri, 1 Jun 2001, Dennis Muhlestein wrote:

>
> If I have table A, which contains a primary key,
> and table B which inherits from A.
>
> How can I Create a Foreign Key on table C that references A, but will
> also pass if a record in inserted in to B.
>
> for instance:
>
> create table A
> (
>         prim_key char(20) not null primary key
> );
>
> create table B
> (
> ) INHERITS A;
>
>
> create table C
> (
>         data char(2) not null primary key,
>         constraint fk_C FOREIGN KEY ( data ) REFERENCES A ( prim_key ) ON DELETE
> CASCADE ON UPDATE CASCADE
> )
>
>
>
> With this setup, my record must be inserted in to A or I'll get a
> referential integrity problem when I insert in to C.
>
> I want to be able to insert in to A, B , or another table inherited from A,
> and have table C recognize that as it's foreign key.