Re: Is the primary key constraint also an index? - Mailing list pgsql-general

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