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