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