Re: foreign key creation problem - Mailing list pgsql-general

Hi Again,
Scratch that. I was running two seperate proceedures on one of the tables,
so the alter table command was blocked. Once I ran it properly, it only took
about 5 minutes. Sorry about that...
adam



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?
>
>
>