Re: Is the primary key constraint also an index?

Tim Uckun wrote:
> If I have a primary key constraint defined in the database do I also
> need to create an index on that field for fast lookup?

No. Declaring field(s) as the primary key automatically adds a UNIQUE
constraint on those fields. PostgreSQL implements unique constraints
using a unique-constrained index.

PostgreSQL tells you about this when you create a table.

craig=> CREATE TABLE j ( y INTEGER PRIMARY KEY );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "j_pkey"
for table "j"

> The documentation on the web seems to imply that the contraint is not
> an index. Is that right?

There are many types of constraint. Unique constraints. Foreign key
constraints. CHECK constraints. Probably more. Of those, unique
constraints are the only ones that will automatically create an index.

Foreign key constraints benefit from an index on the referring field, by
the way, so you should generally create an index on the referring field.
PostgreSQL doesn't do this for you since it's not strictly necessary and
the index does have a space cost and a time cost for updates, inserts
and deletes.

As for CHECK constraints - I strongly recommend reading up on them, as
they're really important for producing schema that properly ensure that
the data stored is valid at all times.

> What the difference between creating a unique, not null index and
> setting a primary key?

As far as I know, a huge amount in purely technical terms. There may
only be one primary key, where there may be several NOT NULL UNIQUE
constrained columns or column sets. Also, some clients rely on the
primary key as table metadata. DBMS front-ends (think MS Access),
reporting tools, etc tend to use this information, as do some ORM tools.

--
Craig Ringer
> Thanks.
>