PRIMARY KEY & INHERITANCE (fwd)
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
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:
Show quoted text
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 foundHow can I create PRIMARY KEY CONSTRAINT for table advert?
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.
Show quoted text
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
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.
Show quoted text
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.
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.
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?)
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.
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
bbbThe 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.
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
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.