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