Hi,

please look at following example:

CREATE TABLE picture (
id serial not null,
description text,
filename text);

CREATE TABLE advert (
artist text,
customer text,
target text)
INHERITS (picture);

CREATE TABLE work (
id serial not null,
advert_id int4 not null references advert,
value numeric(6,2) default 0);

NOTICE:  CREATE TABLE will create implicit sequence 'work_id_seq' for SERIAL
col
umn 'work.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'work_id_key' for
table 
'work'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  PRIMARY KEY for referenced table "advert" not found


How can I create PRIMARY KEY CONSTRAINT for table advert?


Thank you in advance for any reply,           \fer


Tue, 18 Jul 2000 21:11:36 +0100
Ferruccio Zamuner <nonsolosoft@diff.org>
PRIMARY KEY & INHERITANCE (fwd)
Something on the TODO list is that indexes should be inherited by
default. Unfortunately, right now they are not. I'm not sure what the
interaction is here with the foreign key mechanism, so I'm CCing this to
hackers to see if anyone there might comment.

Ferruccio Zamuner wrote:
> 
> Hi,
> 
> please look at following example:
> 
> CREATE TABLE picture (
> id serial not null,
> description text,
> filename text);
> 
> CREATE TABLE advert (
> artist text,
> customer text,
> target text)
> INHERITS (picture);
> 
> CREATE TABLE work (
> id serial not null,
> advert_id int4 not null references advert,
> value numeric(6,2) default 0);
> 
> NOTICE:  CREATE TABLE will create implicit sequence 'work_id_seq' for SERIAL
> col
> umn 'work.id'
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'work_id_key' for
> table
> 'work'
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> ERROR:  PRIMARY KEY for referenced table "advert" not found
> 
> How can I create PRIMARY KEY CONSTRAINT for table advert?

Wed, 19 Jul 2000 13:56:35 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: PRIMARY KEY & INHERITANCE (fwd)
On Wed, 19 Jul 2000, Chris Bitmead wrote:

> Something on the TODO list is that indexes should be inherited by
> default. Unfortunately, right now they are not. I'm not sure what the
> interaction is here with the foreign key mechanism, so I'm CCing this to
> hackers to see if anyone there might comment.

If you don't specify a set of target columns for the reference, it goes to
the primary key of the table (if one exists).  If one doesn't we error out
as shown below.  You can make the reference by saying:
advert_id int4 not null references advert(id) 
in the definition of table work.

Of course, in this case, I don't even see a primary key being defined on
either picture or advert, so it's not really the inheritance thing unless
he also made an index somewhere else (not using unique or primary key on
the table).

In 7.1, the ability to reference columns that are not constrained to be
unique will probably go away, but you can also make the index on
advert(id) to make it happy in that case.

> > CREATE TABLE picture (
> > id serial not null,
> > description text,
> > filename text);
> > 
> > CREATE TABLE advert (
> > artist text,
> > customer text,
> > target text)
> > INHERITS (picture);
> > 
> > CREATE TABLE work (
> > id serial not null,
> > advert_id int4 not null references advert,
> > value numeric(6,2) default 0);
> > 
> > NOTICE:  CREATE TABLE will create implicit sequence 'work_id_seq' for SERIAL
> > col
> > umn 'work.id'
> > NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'work_id_key' for
> > table
> > 'work'
> > NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> > ERROR:  PRIMARY KEY for referenced table "advert" not found


Tue, 18 Jul 2000 22:25:26 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: [HACKERS] Re: PRIMARY KEY & INHERITANCE (fwd)

Of course I had to be half asleep when I wrote the second paragraph of my
response, since I totally missed he was using a serial.  The rest still
applies though...

As an aside to Chris, what interactions do you expect between the OO stuff 
you've been working on and foreign key references?  I'm going to have to
muck around with the trigger code to move to storing oids of tables and
attributes rather than names, so I thought it might make sense to at least
think about possible future interactions.

On Tue, 18 Jul 2000, Stephan Szabo wrote:
> 
> If you don't specify a set of target columns for the reference, it goes to
> the primary key of the table (if one exists).  If one doesn't we error out
> as shown below.  You can make the reference by saying:
> advert_id int4 not null references advert(id) 
> in the definition of table work.
> 
> Of course, in this case, I don't even see a primary key being defined on
> either picture or advert, so it's not really the inheritance thing unless
> he also made an index somewhere else (not using unique or primary key on
> the table).
> 
> In 7.1, the ability to reference columns that are not constrained to be
> unique will probably go away, but you can also make the index on
> advert(id) to make it happy in that case.


Wed, 19 Jul 2000 07:26:24 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: [HACKERS] Re: PRIMARY KEY & INHERITANCE (fwd)
Stephan Szabo wrote:
> 
> Of course I had to be half asleep when I wrote the second paragraph of my
> response, since I totally missed he was using a serial.  The rest still
> applies though...
> 
> As an aside to Chris, what interactions do you expect between the OO stuff
> you've been working on and foreign key references?  I'm going to have to
> muck around with the trigger code to move to storing oids of tables and
> attributes rather than names, so I thought it might make sense to at least
> think about possible future interactions.

As a rule, anything that applies to a base class should also apply to
the sub-class automatically. For some things you may want to have the
option of excluding it, by something like the ONLY syntax of select, but
99% of the time everything should just apply to sub-classes.

Storing oids of attributes sounds like a problem in this context because
it may make it hard to relate these to sub-classes. I do really think
that the system catalogs should be re-arranged so that attributes have
two parts - the parts that are specific to that class, and the parts
that also apply to sub-classes. For example the type and the length
should probably apply to sub-classes. The attnum and the name should
probably be individual to each class in the hierarchy. (The name should
be individual to support subclass renaming to avoid naming conflicts,
like in the draft SQL3 and Eiffel). If it is in two parts then using the
oid of the common part would make it easy for your purposes.

Thu, 20 Jul 2000 09:43:37 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: [HACKERS] Re: PRIMARY KEY & INHERITANCE (fwd)
> As a rule, anything that applies to a base class should also apply to
> the sub-class automatically. For some things you may want to have the
> option of excluding it, by something like the ONLY syntax of select, but
> 99% of the time everything should just apply to sub-classes.
That makes sense.  I assume that you cannot remove the unique constraint
that
a parent provides, once those start being inherited.  This is mostly because
foreign key references really only work in the presence of a unique
constraint.

> Storing oids of attributes sounds like a problem in this context because
> it may make it hard to relate these to sub-classes. I do really think
> that the system catalogs should be re-arranged so that attributes have
> two parts - the parts that are specific to that class, and the parts
> that also apply to sub-classes. For example the type and the length
> should probably apply to sub-classes. The attnum and the name should
> probably be individual to each class in the hierarchy. (The name should
> be individual to support subclass renaming to avoid naming conflicts,
> like in the draft SQL3 and Eiffel). If it is in two parts then using the
> oid of the common part would make it easy for your purposes.
How would one refer to an attribute whose name has changed in a
subclass if you're doing a select on the superclass (or do you even
need to do anything - does it figure it out automagically?)



Wed, 19 Jul 2000 17:18:17 -0700
"Stephan Szabo" <sszabo@kick.com>
Re: Re: [GENERAL] PRIMARY KEY & INHERITANCE (fwd)
Stephan Szabo wrote:

> > Storing oids of attributes sounds like a problem in this context because
> > it may make it hard to relate these to sub-classes. I do really think
> > that the system catalogs should be re-arranged so that attributes have
> > two parts - the parts that are specific to that class, and the parts
> > that also apply to sub-classes. For example the type and the length
> > should probably apply to sub-classes. The attnum and the name should
> > probably be individual to each class in the hierarchy. (The name should
> > be individual to support subclass renaming to avoid naming conflicts,
> > like in the draft SQL3 and Eiffel). If it is in two parts then using the
> > oid of the common part would make it easy for your purposes.
> How would one refer to an attribute whose name has changed in a
> subclass if you're doing a select on the superclass (or do you even
> need to do anything - does it figure it out automagically?)

If you had..
create table a (aa text);
create table b under a rename aa to bb ( );
insert into a(aa) values('aaa');
insert into b(bb) values('bbb');
select * from a;

aa
---
aaa
bbb

The system knows that a.aa is the same as b.bb. The same attribute
logically, just referred to by different names depending on the context.
Eiffel handles it the same way if I remember right.

Thu, 20 Jul 2000 10:31:37 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: Re: [GENERAL] PRIMARY KEY & INHERITANCE (fwd)


> > How would one refer to an attribute whose name has changed in a
> > subclass if you're doing a select on the superclass (or do you even
> > need to do anything - does it figure it out automagically?)
> 
> If you had..
> create table a (aa text);
> create table b under a rename aa to bb ( );
> insert into a(aa) values('aaa');
> insert into b(bb) values('bbb');
> select * from a;
> 
> aa
> ---
> aaa
> bbb
> 
> The system knows that a.aa is the same as b.bb. The same attribute
> logically, just referred to by different names depending on the context.
> Eiffel handles it the same way if I remember right.

So, if you did, select * from a where aa>'a', it would properly mean 
the inherited attribute, even if an attribute aa was added to table b, 
possibly of a different type?  In that case I really wouldn't need to do 
anything special to handle the subtables since I'd always be doing the 
select for update on the table that was specified at creation time which
is the one I have the attributes for.



Wed, 19 Jul 2000 17:50:06 -0700
"Stephan Szabo" <sszabo@kick.com>
Re: Re: [GENERAL] PRIMARY KEY & INHERITANCE (fwd)
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes:
> ... The attnum and the name should
> probably be individual to each class in the hierarchy. (The name should
> be individual to support subclass renaming to avoid naming conflicts,
> like in the draft SQL3 and Eiffel). If it is in two parts then using the
> oid of the common part would make it easy for your purposes.

This bothers me.  Seems like you are saying that a subclass's column
might not match the parent's by *either* name or column position, but
nonetheless the system will know that this subclass column is the same
as that parent column.  No doubt we could implement that by relying on
OIDs of pg_attribute rows, but just because it's implementable doesn't
make it a good idea.  I submit that this is too confusing to be of
any practical use.  There should be a *user-visible* connection between
parent and child column, not some magic under-the-hood connection.
IMHO it ought to be the column name.

			regards, tom lane

Thu, 20 Jul 2000 00:22:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: PRIMARY KEY & INHERITANCE (fwd) 
Tom Lane wrote:
> 
> Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes:
> > ... The attnum and the name should
> > probably be individual to each class in the hierarchy. (The name should
> > be individual to support subclass renaming to avoid naming conflicts,
> > like in the draft SQL3 and Eiffel). If it is in two parts then using the
> > oid of the common part would make it easy for your purposes.
> 
> This bothers me.  Seems like you are saying that a subclass's column
> might not match the parent's by *either* name or column position, but
> nonetheless the system will know that this subclass column is the same
> as that parent column.  No doubt we could implement that by relying on
> OIDs of pg_attribute rows, but just because it's implementable doesn't
> make it a good idea.  I submit that this is too confusing to be of
> any practical use.  There should be a *user-visible* connection between
> parent and child column, not some magic under-the-hood connection.
> IMHO it ought to be the column name.

When you multiple inherit from unrelated base classes you need a
conflict
resolution mechanism. That's why it can't be the name. The SQL3 draft
recognised this.

Many programming languages deal with this issue without undue confusion.
To provide mapping to these programming languages such a conflict
resolution mechanism becomes necessary.

Thu, 20 Jul 2000 14:56:42 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: [HACKERS] Re: PRIMARY KEY & INHERITANCE (fwd)