Обсуждение: foreign key creation problem
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) thanks adam
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?
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? > > >
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@megazone23.bigpanda.com] Sent: Thursday, 13 December 2001 3:52 To: Adam Fisher Cc: pgsql-general@postgresql.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?
I have a price field of type 'money'. 90% of the time, the output format of money $x.xx, is great. But occasionally I need to strip the $ and truncate the decimal places, i.e. turn it into an int. Is there any easy way to do this? None of the built-in postgres text formatting functions take the money type as an arg. I tried type casting, but that didn't work either. I tried to check the mail list archives, but it's been down for about 2-3 days. Error I get is An error occured! connectDBStart() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'db.postgresql.org' and accepting connections on TCP/IP port 5437? Thanks much, Rich
How Can I reach the mail archieve ? Serkan
fts.postgresql.org -or- archives.postgresql.org same archives, just different views on them ... On Thu, 13 Dec 2001, Serkan BEKTAS wrote: > > How Can I reach the mail archieve ? > > Serkan > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Have you thought about using the numeric type. I believe that they money type is deprecated. Jason "Rich Ryan" <rich@usedcars.com> writes: > I have a price field of type 'money'. 90% of the time, the output > format of money $x.xx, is great. But occasionally I need to strip > the $ and truncate the decimal places, i.e. turn it into an int. Is > there any easy way to do this? None of the built-in postgres text > formatting functions take the money type as an arg. I tried type > casting, but that didn't work either. I tried to check the mail list > archives, but it's been down for about 2-3 days. Error I get is An > error occured! connectDBStart() -- connect() failed: Connection > refused Is the postmaster running (with -i) at 'db.postgresql.org' > and accepting connections on TCP/IP port 5437? > > Thanks much, > > Rich > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Fri, 14 Dec 2001, adam fisher wrote: > 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... No problem. The implementation of doing the check is not the best (see recent conversation in hackers if you're interested) and can be a bit slow.
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@megazone23.bigpanda.com] Sent: Thursday, 13 December 2001 3:52 To: Adam Fisher Cc: pgsql-general@postgresql.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?
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? > > >
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? > > >