Обсуждение: 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.
---------------------------------------------------------------

I believe you will have to say good bye to the foreign key constraint.

Its is not inherited, only the schema is, constraints and index will
have to be separately created per child table.


-Regards

Kedar Parikh


Quoting Luiz Eduardo Cantanhede Neri <lecneri@gmail.com>:

> Hi,
> I'm trying to do something like this:
>
> BEGIN;
>
> CREATE TABLE A
> (
> idA serial not null primary key,
> someColumn text
> );
>
> CREATE TABLE B
> (
> someData text
> ) INHERITS(A);
>
> CREATE TABLE C
> (
> idC serial not null primary key,
> anotherColumn text
> );
>
> CREATE TABLE BC(
> idB int4,
> idC int4,
>  FOREIGN KEY (idB) REFERENCES B(idA),
>  FOREIGN KEY (idC) REFERENCES C(idC)
> );
>
> COMMIT;
>
> 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?
>
> Thx
> Luiz Eduardo
>

Luiz Eduardo Cantanhede Neri <lecneri@gmail.com> writes:
> My question is: Shouldn't it get Table A primary key and create the foreign
> key?

Unique constraints aren't inherited at the moment.

            regards, tom lane

We use partitioning and inheritance to a great extent to manage our
massive tables, I just hope that partitioning will be as simple,
effective and free from hacks and work around as it is in Oracle.

I love postgres otherwise.

- Kedar Parikh

Quoting Luiz Eduardo Cantanhede Neri <lecneri@gmail.com>:

> Just a correction, I were recreating Primary key wrongly. Recreating the
> primary key @ child table worked too
>
> On Sun, May 24, 2009 at 3:26 PM, Luiz Eduardo Cantanhede Neri <
> lecneri@gmail.com> wrote:
>
>> My workaround to this was create a unique key serial @ childtable and use
>> this unique key as foreign key. But I really dont like this.
>>
>> I tried to recreate the primary key again, but didnt work either
>>
>>
>> On Sun, May 24, 2009 at 3:11 PM, Kedar Rasik Parikh
>> <kedarr@netcore.co.in>wrote:
>>
>>> I believe you will have to say good bye to the foreign key constraint.
>>>
>>> Its is not inherited, only the schema is, constraints and index will have
>>> to be separately created per child table.
>>>
>>>
>>> -Regards
>>>
>>> Kedar Parikh
>>>
>>>
>>>
>>> Quoting Luiz Eduardo Cantanhede Neri <lecneri@gmail.com>:
>>>
>>>  Hi,
>>>> I'm trying to do something like this:
>>>>
>>>> BEGIN;
>>>>
>>>> CREATE TABLE A
>>>> (
>>>> idA serial not null primary key,
>>>> someColumn text
>>>> );
>>>>
>>>> CREATE TABLE B
>>>> (
>>>> someData text
>>>> ) INHERITS(A);
>>>>
>>>> CREATE TABLE C
>>>> (
>>>> idC serial not null primary key,
>>>> anotherColumn text
>>>> );
>>>>
>>>> CREATE TABLE BC(
>>>> idB int4,
>>>> idC int4,
>>>>  FOREIGN KEY (idB) REFERENCES B(idA),
>>>>  FOREIGN KEY (idC) REFERENCES C(idC)
>>>> );
>>>>
>>>> COMMIT;
>>>>
>>>> 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?
>>>>
>>>> Thx
>>>> Luiz Eduardo
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>

--As of May 25, 2009 12:57:51 AM +0530, Kedar Rasik Parikh is alleged to
have said:

> We use partitioning and inheritance to a great extent to manage our
> massive tables, I just hope that partitioning will be as simple,
> effective and free from hacks and work around as it is in Oracle.
>
> I love postgres otherwise.

--As for the rest, it is mine.

Partitioning under Postgres is simple, and works fairly well from what I've
seen, with one big cravat:

Partitioning and foreign keys do not mix.  You can create a foreign key
from one partitioned table to another table, or to a specific partition,
but not to the top level of a partitioned table.  Not and have it work,
anyway.  (The key will see only the top-level table, which you've probably
got set up to be empty.)

Basically, figure you can create a foreign key from a partitioned table,
but not one that references one.  (At least, not without hackery.)

There are a couple of other things that you'll probably have to do
manually, (keys in general don't inherit, so you'll want to re-create them
on each partition, and the query planner may not see that a constraint is
being satisfied if it's not directly listed in the where clause) but that's
the big hole in Postgres' partitioning at the moment, as far as I can see.

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.
---------------------------------------------------------------