I don't believe it!
In my haste to find a greater problem, I ignored case-sensitivity. Thank you
very much for pointing that out, it's working now.
However, can I now ask another question?
The item2 table has about 450,000 lines in it, and the inventory table has
about 89000. I have been running the alter table command to add the foreign
key for about 8 hours now, and it's still going. Is there anything I can do
to speed it up. Would it have helped if the field that the foreign key
constraint is applied to was indexed?
Thanks again,
adam
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, 13 December 2001 5:33
To: adam fisher
Cc: 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] 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?
>
>
>
