another foreign key question

From: "Adam Fisher" <black(at)cia(dot)com(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: another foreign key question
Date: 2001-12-18 22:51:20
Message-ID: 9vohag$30f1$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Koenig 2001-12-18 22:53:39 Re: Way to use count() and LIMIT?
Previous Message Ian Harding 2001-12-18 22:44:19 Re: More on Too Many Open Files... NetBSD