Re: Is the primary key constraint also an index?

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Tim Uckun
> Sent: Tuesday, August 12, 2008 7:18 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Is the primary key constraint also an index?
>
> 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?
>
> The documentation on the web seems to imply that the contraint is not
> an index. Is that right?
>
> What the difference between creating a unique, not null index and
> setting a primary key?

From Bruce Momjian's book:
"PRIMARY KEY
The PRIMARY KEY constraint, which marks the column that uniquely identifies each row, is a combination of UNIQUE and NOT NULL constraints. With this type of constraint, UNIQUE prevents duplicates, and NOT NULL prevents NULL values in the column. The next figure shows the creation of a PRIMARY KEY column.

test=> CREATE TABLE primarytest (col INTEGER PRIMARY KEY);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest_pkey' for table 'primarytest'
CREATE
test=> \d primarytest
Table "primarytest"
Attribute | Type | Modifier
-----------+---------+----------
col | integer | not null
Index: primarytest_pkey

Notice that an index is created automatically, and the column is defined as NOT NULL.

Just as with UNIQUE, a multicolumn PRIMARY KEY constraint must be specified on a separate line. In the next figure, col1 and col2 are combined to form the primary key.

test=> CREATE TABLE primarytest2 (
test(> col1 INTEGER,
test(> col2 INTEGER,
test(> PRIMARY KEY(col1, col2)
test(> );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest2_pkey' for table 'primarytest2'
CREATE

A table cannot have more than one PRIMARY KEY specification. Primary keys have special meaning when using foreign keys, which are covered in the next section."
========================================================
While this bit of the documentation about primary key does not make the index relationship clear:
========================================================
"PRIMARY KEY (column constraint)
PRIMARY KEY ( column_name [, ... ] ) (table constraint)
The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows.

Only one primary key can be specified for a table, whether as a column constraint or a table constraint.

The primary key constraint should name a set of columns that is different from other sets of columns named by any unique constraint defined for the same table."
========================================================
This bit makes it totally obvious:
========================================================
"USING INDEX TABLESPACE tablespace
This clause allows selection of the tablespace in which the index associated with a UNIQUE or PRIMARY KEY constraint will be created. If not specified, default_tablespace is consulted, or temp_tablespaces if the table is temporary."
========================================================
See:
http://www.postgresql.org/docs/8.3/static/sql-createtable.html