Subject Re: FK confusion
Author Adam
> > Except for the odd primary key, I guess...
> History is the millstone around my neck too. The design was
originally for
> Paradox for DOS. That primary key is not the only thing on the list
> 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.

What Ann is referring to is this:

CONSTRAINT "PK_Cust" PRIMARY KEY ("Cat", "Last Name", "ID")

and this:


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
key as:


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
unique anyway.

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.