Thread: 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: > > 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?
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
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.
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.
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.