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") — a_horse_with_no_name, Feb 01 '13 at 12:14

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

57

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

1 Answers1