Isn't a PK automatically a proper unique indexed key

Question

I'm checking out some newish features of Apache Derby for my Java EE app. IntelliJ provides helpful shortcuts for creating a simple DB schema. After a few clicks I have

create table "Names"
(
    id int generated always as identity
        constraint NAMES_PK
            primary key
);

This should, to me, create a proper lookup table, hash or map, for the PK.

If I add the criteria that PK needs to be unique (?)

create unique index NAMES_ID_UINDEX
on "Names" (id);

So my Q is, does this change the how the PK lookup is implemented? The docs are quite unclear on this. This is Derby 10.15.2.0.

score 2 · Accepted Answer · answered Dec 28 '21 at 01:30

I have trouble parsing your question, but I'll try to answer.

Isn't a PK automatically a proper unique indexed key

You are conflating logical and physical aspects of the database model in a single sentence. A primary key by definition uniquely identifies rows in a relation -- but this is a logical construct. Depending on the particular DBMS a PK may be purely declarative, and thus not enforced by the engine in any way, or it can be backed by something that enforces its properties (absence of null values and uniqueness), such as a NOT NULL constraint and a unique index. The latter is the case with Derby, docs seem to be quite clear on that:

UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints generate indexes that enforce or "back" the constraint (and are sometimes called backing indexes). UNIQUE and PRIMARY KEY constraints generate unique indexes.

So, separately creating a unique index is redundant. Some DBMSes, but not Derby, would prevent you from even creating an index that has the same list of columns as an existing index.

does this [adding a unique index on the primary key column] change the how the PK lookup is implemented?

No. You will have two identical indexes and the optimizer will choose one of them, when appropriate, with equal effect.

Isn't a PK automatically a proper unique indexed key

1 Answers1