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.

This article mentions Postgres multiple times.  It even mentions
GreatBridge:

http://dailynews.yahoo.com/h/zd/20010606/tc/linux_forklifts_in_the_data_warehouse_1.html

Brent

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year!  http://personal.mail.yahoo.com/

> This article mentions Postgres multiple times.  It even mentions
> GreatBridge:
>
> http://dailynews.yahoo.com/h/zd/20010606/tc/linux_forklifts_in_the_data_warehouse_1.html

This was an interesting article.  I saw:

    For those in Japan who don't need all the horsepower of Oracle, Miracle
    Linux offers a Linux/PostgreSQL bundle.  Another Japanese company,
    Software Research Associates, also supports the PostgreSQL, though I
    don't know if I like its turtle logo.

Now Miracle is a subsidiary of Oracle, so Oracle is actually selling
PostgreSQL.  Strange.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

> > This article mentions Postgres multiple times.  It even mentions
> > GreatBridge:
> >
> > http://dailynews.yahoo.com/h/zd/20010606/tc/linux_forklifts_in_the_data_warehouse_1.html
>
> This was an interesting article.  I saw:
>
>     For those in Japan who don't need all the horsepower of Oracle, Miracle
>     Linux offers a Linux/PostgreSQL bundle.  Another Japanese company,
>     Software Research Associates, also supports the PostgreSQL, though I
>     don't know if I like its turtle logo.
>
> Now Miracle is a subsidiary of Oracle, so Oracle is actually selling
> PostgreSQL.  Strange.

Yes, strange but true. They seem to hope some of PostgreSQL users
would eventually upgrade to Oracle.
--
Tatsuo Ishii