Thread: 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?

Thanks.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Tim Uckun
> Sent: Tuesday, August 12, 2008 7:18 PM
> To: pgsql-general@postgresql.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
NOTNULL 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,
col1and 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,
whichare 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),
nonnullvalues. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of
columnsas primary key also provides metadata about the design of the schema, as a primary key implies that other tables
canrely 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
uniqueconstraint 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
willbe 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

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.
>

On 13/08/2008 03:18, 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?

As I understand it, creating a primary key on a table also creates an
index automatically:

testdb=# create table test(f1 integer, f2 text);
CREATE TABLE
testdb=# alter table test add constraint test_pk primary key(f1);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
test_pk" for table "test"
ALTER TABLE


Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------