> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] 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 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
