|Subject||Re: FK confusion|
> > Except for the odd primary key, I guess...
> History is the millstone around my neck too. The design was
> Paradox for DOS. That primary key is not the only thing on the listWhat Ann is referring to is this:
> of changes, but I have to get it converted as-is first. The only
> thing that's saving me is that this is a relatively tiny database,
> so even TTable analogs will probably work well enough to get by.
CONSTRAINT "PK_Cust" PRIMARY KEY ("Cat", "Last Name", "ID")
CREATE UNIQUE INDEX "IDX_Cust_13" ON "Cust" (ID);
If a part of a composite key is uniquely constrained, then there is no
need to define a unique constraint across the key. If I have fields A,
B and C. And I tell you that C is always unique, then you don't need
to tell me that the combination A,B,C is unique - that is given.
I suggest you drop that primary key, and instead create your primary
CONSTRAINT "PK_Cust" PRIMARY KEY ("ID")
This will meet your need of a unique constraint and also your need of
an index on that field.
I would also add an index (if required) on:
CREATE INDEX "IDX_Cust_OldPK" ON "Cust" ("Cat", "Last Name", "ID");
There is no need to define it as unique because ID is guaranteed
Firebird does not store records in order of primary key, so there is
no performance benefit in setting particular fields as the primary key
just because that is how you report on it.