Обсуждение: BUG #1514: Inheritance and Primary Keys

The following bug has been logged online:

Bug reference:      1514
Logged by:          Peter Newman
Email address:      pnewman@wanadoo.nl
PostgreSQL version: 7.4.7
Operating system:   Fedora Core 3
Description:        Inheritance and Primary Keys
Details:

Hi

Just exploring the use of inheritance to so some design problems and found
some strange behaviour.  Not sure if this is a bug or feature?

From
http://www.postgresql.org/docs/8.0/interactive/tutorial-inheritance.html

create table cities (id serial primary key, name varchar(45));
create table capitals (state char(2)) inherits (cities);

insert into cities values (1, 'Paris');
insert into cities values (1, 'London');

The second one fails naturally however if you were to
insert into capitals values (1, 'Paris', 'FR');
insert into capitals values (1, 'London', 'UK');

This is fine.  And if
create table capitals (state char(2), primary key (id)) inherits (cities);
create table non_capitals (state char(2), primary key (id)) inherits
(cities);
then
insert into capitals values (1, 'Paris', 'FR');
insert into non_capitals values (1, 'London', 'UK');

is also okay.

If you were to insert data like this:
insert into capitals (name, state) values ('Paris', 'FR');
insert into non_capitals (name, state) values ('London', 'UK');

then the ids in cities would be 1 and 2 using the serial sequencing.  This
seems inconsistent at least.

So my point is (if it is not clear) that the primary key constraint is not
enforced through subclass tables.  Is this intentional?

Cheers,
Pete.

On Wed, Mar 02, 2005 at 03:23:27PM +0000, Peter Newman wrote:

> So my point is (if it is not clear) that the primary key constraint is not
> enforced through subclass tables.  Is this intentional?

It's a documented limitation of the current implementation.  Here's
an excerpt from the "Inheritance" section of the "Data Definition"
chapter of the documentation:

    A limitation of the inheritance feature is that indexes (including
    unique constraints) and foreign key constraints only apply to
    single tables, not to their inheritance children.

http://www.postgresql.org/docs/8.0/interactive/ddl-inherit.html

Fixing inheritance limitations is on the developers' TODO list --
it's just a matter of somebody deciding it's important enough to
work on.

http://developer.postgresql.org/todo.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/