Re: [Modeling-users] Re: Implementing inheritance through vertical mapping

[Federico H. <fh...@vi...>]

On Thu, 2003-09-25 at 15:50, Ernesto Revilla wrote:
> Take into account that in PostgreSQL inherited tables have separate
> primary key sets

I understand this is not true, and this transcript seems to disprove it
(unless I failed to understand your meaning of "separate primary key
sets"):

test=3D# create table foo (id serial primary key, foo text);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL=
 column 'foo.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' f=
or table 'foo'
CREATE
test=3D# create table foobar (bar text) inherits (foo);
CREATE
test=3D# insert into foo (foo) values ('hello');
INSERT 981730 1
test=3D# insert into foobar (foo, bar) values ('1,2', '3');
INSERT 981731 1
test=3D# select * from foo;
 id |  foo =20
----+-------
  1 | hello
  2 | 1,2
(2 rows)

test=3D# select * from foobar;
 id | foo | bar=20
----+-----+-----
  2 | 1,2 | 3
(1 row)

>  and that, AFAIK, referential integrity rules in base tables are not
> checked.

It's actually a bit worse that that: they are checked... but they don't
take inheritance into account. This means that if you declare a
relationship to foo, this won't let the foreign key point to a foobar
record:

test=3D# create table bar (id serial primary key, bar int references foo);
NOTICE:  CREATE TABLE will create implicit sequence 'bar_id_seq' for SERIAL=
 column 'bar.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'bar_pkey' f=
or table 'bar'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check=
(s)
CREATE
test=3D# insert into bar (bar) values (1);
INSERT 981752 1
test=3D# insert into bar (bar) values (2);
ERROR:  <unnamed> referential integrity violation - key referenced from bar=
 not found in foo

Which is ugly, and just may make the whole thing unusable for vertical
mapping, but I'm still looking.

	Fede