Re: foreign key creation problem

Hi Stephan,
Schemas as requested:

CREATE TABLE "invtest" (
"invid" integer DEFAULT nextval('"invtest_invid_seq"'::text) NOT
NULL,
"orderline" numeric(8,0),
"location" character varying(35),
"sellprice" numeric(7,2),
"stockno" integer,
"label" character(1),
"indate" date,
"unitid" numeric(8,0),
"qty" numeric(5,0) NOT NULL,
"reord" numeric(2,0),
"comno" numeric(14,0),
"taxdue" numeric(7,2),
"taxfree" numeric(7,2),
"conline" numeric(8,0),
"discom" numeric(14,0),
CONSTRAINT "invtest_qty" CHECK ((qty >= '0'::"numeric")),
Constraint "invtest_pkey" Primary Key ("invid"));

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"));

CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER INSERT OR UPDATE ON "item2" FROM "fmt"
NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"
('<unnamed>', 'item2', 'fmt', 'UNSPECIFIED', 'fmt', 'fmt');

CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER INSERT OR UPDATE ON "item2" FROM "genre"
NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"
('<unnamed>', 'item2', 'genre', 'UNSPECIFIED', 'genre', 'gencode');

-----Original Message-----
From: Stephan Szabo [mailto:sszabo(at)megazone23(dot)bigpanda(dot)com]
Sent: Thursday, 13 December 2001 3:52
To: Adam Fisher
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] foreign key creation problem

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?