Re: foreign key creation problem

On Thu, 13 Dec 2001, adam fisher wrote:

> CREATE TABLE "item2" (
> "stockno" integer DEFAULT nextval('"item2_stockno_seq"'::text) NOT
> NULL,
> "artist" character varying(40) NOT NULL,
> "title" character varying(40) NOT NULL,
> "fmt" character(3) NOT NULL,
> "country" character varying(6),
> "comment" character varying(255),
> "apn" character(14),
> "catno" character varying(25),
> "sup" character(3),
> "collectors" character(1) DEFAULT 'n',
> "genre" character varying(10),
> "shopcom" character varying(50),
> Constraint "item2_pkey" Primary Key ("stockno"));

I'm assuming item from the statement below and item2 here
are the same structure? It looks like you may have created the table,
possibly with a stockNo, but not in double quotes which would have rolled
the real name of the column to "stockno" which won't match the "stockNo"
below.

> On Thu, 13 Dec 2001, Adam Fisher wrote:
>
> > Hi All,
> > I am trying to add a foreign key comstraint to an already-populated table
> > using the ALTER TABLE command. I am linking to the primary key of the
> master
> > table, and the slave table also has a primary key, however the field I am
> > adding the constraint to is not indexed.
> > When I try and create the constraint using:
> > alter table inventory
> > add constraint fk_inv_item_stkNo
> > foreign key ("stockNo") references "item" ("stockNo")
> >
> > i get the following message:
> >
> > UNIQUE constraint matching given keys for referenced table not found
> "item"
> >
> > Can anybody tell me what I'm doing wrong? The field stockNo in the item
> > table is the primary key, so it is unique. Both fields have the dataType
> > integer and the primary key is also a sequence (i.e auto-incrementing)
>
> Can you send the full schema of the tables involved?
>
>
>