Re: INHERIT and FOREIGN KEY issues

--As of May 24, 2009 2:54:47 PM -0300, Luiz Eduardo Cantanhede Neri is
alleged to have said:

> CREATE TABLE A
> (
> idA serial not null primary key,
> someColumn text
> );
>
> CREATE TABLE B
> (
> someData text
> ) INHERITS(A);
...
> CREATE TABLE BC(
> idB int4,
> idC int4,
>  FOREIGN KEY (idB) REFERENCES B(idA),
>  FOREIGN KEY (idC) REFERENCES C(idC)
> );
..
> But When it is going to create the table BC, it gives an error:
>
> ERROR:  there is no unique constraint matching given keys for referenced
> table "b"
>
> My question is: Shouldn't it get Table A primary key and create the
> foreign key?

--As for the rest, it is mine.

Perhaps it should, but it doesn't: Primary Keys are not inherited. From
the docs (emphasis by me):

> All check constraints and not-null constraints on a parent table are
> automatically inherited by its children. Other types of constraints
> (unique, *primary key*, and foreign key constraints) are not inherited.

You'll want to put in an 'ALTER TABLE B ADD PRIMARY KEY("idA");' in there.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------