Postgres Pro
Downloads
Home   >   mailing lists

Thread: Inheritance, shared primary keys between parent and child tables.

Inheritance, shared primary keys between parent and child tables.

From
James McCarthy
Date:
I thought the primary key was NOT shared between parent/child tables through inheritance.

On reading the documentation section for inheritance in Postgresql
 It seems obvious that the primary key of the parent table is not shared by the child table(am I miss-understanding?)

My experience from playing around with this indicates otherwise.

For example

  CREATE TABLE account (
 id SERIAL PRIMARY KEY
);

  CREATE TABLE account_admin (
 cash INTEGER
)INHERITS (account);

  CREATE TABLE account_user (
 credit INTEGER
)INHERITS (account);


INSERT INTO account_admin(cash) VALUES(12);
INSERT INTO account_user(credit) VALUES(12);
INSERT INTO account_user(credit) VALUES(12);
INSERT INTO account_admin(cash) VALUES(12);

My understanding of the documentation is:

SELECT id FROM account_admin;

should return 1,2

actually returns: 1,4;

And:

SELECT id FROM account_user;
should return: 1,2
actually returns: 2,3

While 
SELECT id FROM account;
returns: 1,2,3,4 as expected


Does this mean that child tables of a parent share the same primary key?



Re: Inheritance, shared primary keys between parent and child tables.

From
David Johnston
Date:
James McCarthy wrote
> I thought the primary key was NOT shared between parent/child tables
> through inheritance.
>
> On reading the documentation section for inheritance in Postgresql
> http://www.postgresql.org/docs/9.1/static/ddl-inherit.html
>  It seems obvious that the primary key of the parent table is not shared
> by
> the child table(am I miss-understanding?)
>
> My experience from playing around with this indicates otherwise.
>
> For example
>
>   CREATE TABLE account (
>  id SERIAL PRIMARY KEY
> );
>
>   CREATE TABLE account_admin (
>  cash INTEGER
> )INHERITS (account);
>
>   CREATE TABLE account_user (
>  credit INTEGER
> )INHERITS (account);
>
>
> INSERT INTO account_admin(cash) VALUES(12);
> INSERT INTO account_user(credit) VALUES(12);
> INSERT INTO account_user(credit) VALUES(12);
> INSERT INTO account_admin(cash) VALUES(12);
>
> My understanding of the documentation is:
>
> SELECT id FROM account_admin;
>
> should return 1,2
>
> actually returns: 1,4;
>
> And:
>
> SELECT id FROM account_user;
> should return: 1,2
> actually returns: 2,3
>
> While
> SELECT id FROM account;
> returns: 1,2,3,4 as expected
>
>
> Does this mean that child tables of a parent share the same primary key?

No.  It means they share the same sequence for generating DEFAULTs (i.e. it
copies the end-result of a successful SERIAL type construction - not the
SERIAL pseudo-type itself).  Try specifying manual IDs and see what happens.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Inheritance-shared-primary-keys-between-parent-and-child-tables-tp5786288p5786295.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.