Thread: another foreign key question
Hi all, I am trying to add a foregin key constraint to a non-indexed field of a table that has 212195 rows. It is referencing the primary key of a table that has 89060 rows. The schemas are below. The command I used is: alter table invhistory add constraint fk_invhist_invid_inventory foreign key ("invid") references "inventory" ("invid") The proceedure seems to be working, however it's now been 17 hours and, from what I can tell, it's still going. Does this hint at a problem? If not, is there any way I could speed up subsequent foreign key operations as I have quite a few more that I want to add to my database on tables of similar size? If I indexed the field before adding the foreign key constraint, would that speed things up? Also, is there anyway that I can tell if it's still working, as opposed to being stalled, locked or anything? (Shouldn't be locked, as no-one else has access to this box and I only have one instance of psql running, but for my own knowledge...) Thanks very much, adam schemas: CREATE TABLE "inventory" ( "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") ); Above table has 89060 rows CREATE TABLE "invhistory" ( "histno" integer DEFAULT nextval('"invhistory_histno_seq"'::text) NOT NULL, "invid" numeric(14,0), "indate" date NOT NULL, "qty" numeric(18,0) NOT NULL, "sellprice" numeric(7,2), Constraint "invhistory_pkey" Primary Key ("histno") ); Above table has 212195 rows
On Wed, 19 Dec 2001, Adam Fisher wrote: > I am trying to add a foregin key constraint to a non-indexed field of a > table that has 212195 rows. It is referencing the primary key of a table > that has 89060 rows. The schemas are below. The command I used is: > > alter table invhistory > add constraint fk_invhist_invid_inventory > foreign key ("invid") references "inventory" ("invid") > > The proceedure seems to be working, however it's now been 17 hours and, from > what I can tell, it's still going. Does this hint at a problem? If not, is > there any way I could speed up subsequent foreign key operations as I have > quite a few more that I want to add to my database on tables of similar > size? Hmm, maybe, maybe not. It's doing a copy of the trigger for each row which will take a while, although I'm surprised it's that long. > If I indexed the field before adding the foreign key constraint, would that > speed things up? Also, is there anyway that I can tell if it's still AFAIK Not for the adding of the constraint. It should speed up the update/delete checks on inventory, however. > working, as opposed to being stalled, locked or anything? (Shouldn't be > locked, as no-one else has access to this box and I only have one instance > of psql running, but for my own knowledge...) I'd suggest checking ps or the backend log if you've got debug turned up.
On Wed, 19 Dec 2001, Adam Fisher wrote: > alter table invhistory > add constraint fk_invhist_invid_inventory > foreign key ("invid") references "inventory" ("invid") Wasn't there something about foreign keys having to be of the same data type? (This pops to mind without doing a search of the archives) The inventory table is using integer (INT4?) and the invhistory uses NUMERIC(14,0). Seems a cast between types will be some extra overhead. Isn't NUMERIC what was called BCD (binary coded decimal) in-the-olden-days. Rod -- Let Accuracy Triumph Over Victory Zetetic Institute "David's Sling" Marc Stiegler