Do I need to create separate index for primary key of relational database table

Question

If I create table with primary key is index automatically created for the table or does that need doing separately.

i.e if this is the table ddl

 CREATE TABLE release(guid varchar(36) NOT NULL PRIMARY KEY,
name varchar(255),xmldata  CLOB(512 K))

do I also need to do

CREATE INDEX release_idx ON release(guid)

or not

(I'm using Derby a database that comes with Java)

This all documented in the manual: http://db.apache.org/derby/docs/10.9/ref/rrefsqlj13590.html#rrefsqlj13590 (see the section "Backing indexes") — , Feb 01 '13 at 12:14

score 60 · Accepted Answer · answered Feb 01 '13 at 12:12

60

You don't need to. The primary key is already an index.

answered Feb 01 '13 at 12:12

Jon

Good to know I thought so but wasn't sure – Paul Taylor Feb 01 '13 at 12:30
1

@Jon Just out of curiosity does the performance of query improve if we built again an index on primary key? – RKR Jan 20 '17 at 05:20
The `PRIMARY KEY` is also `UNIQUE` _in MySQL_. – Rick James Oct 31 '17 at 19:08
@RickJames PRIMARY KEY has to be unique by it's definition. Not only in MySQL but in every rdbms – Łukasz Pijet Dec 17 '17 at 21:48
Sorry for being pedantic, but the primary key is a constraint, not an index. – tonysepia Aug 05 '18 at 12:16
The Derby Reference Guide mentions that [Derby automatically creates an index](https://db.apache.org/derby/docs/10.14/ref/rrefsqlj20937.html) when you add a primary key constraint. In this way your primary key will get an index automatically. The Derby Reference also mentions how to find the name of the auto-created index which backs the primary key constraint. – MikeOnline Aug 09 '23 at 04:36

1 Answers1