</indexterm>

<para>

Unique constraints ensure that the data contained in a column or a

group of columns is uniquewith respect to all the rows in the

Unique constraints ensure that the data contained in a column, or a

group of columns, is uniqueamong all the rows in the

table. The syntax is:

<programlisting>

CREATE TABLE products (

</para>

<para>

Ifa unique constraintrefers toa group of columns,the columns

are listed separated by commas:

To definea unique constraintfora group of columns,write it as a

table constraint with the column names separated by commas:

<programlisting>

CREATE TABLE example (

a integer,

</para>

<para>

Adding a unique constraint will automatically create a unique btree

index on the column or group of columns used in the constraint.

A uniqueness constraint on only some rows can be enforced by creating

a <link linkend="indexes-partial">partial index</link>.

Adding a unique constraint will automatically create a unique B-tree

index on the column or group of columns listed in the constraint.

A uniqueness restriction covering only some rows cannot be written as

a unique constraint, but it is possible to enforce such a restriction by

creating a unique <link linkend="indexes-partial">partial index</link>.

</para>

<indexterm>

</indexterm>

<para>

In general, a unique constraint is violatedwhen there is more than

In general, a unique constraint is violatedif there is more than

one row in the table where the values of all of the

columns included in the constraint are equal.

However, two null values arenot considered equal in this

However, two null values arenever considered equal in this

comparison. That means even in the presence of a

unique constraint it is possible to store duplicate

rows that contain a null value in at least one of the constrained

</indexterm>

<para>

Technically, a primary key constraint is simply a combination of a

unique constraint and a not-null constraint. So, the following

A primary key constraint indicates that a column, or group of columns,

can be used as a unique identifier for rows in the table. This

requires that the values be both unique and not null. So, the following

two table definitions accept the same data:

<programlisting>

CREATE TABLE products (

</para>

<para>

Primary keys canalso constrain more than one column; the syntax

Primary keys canspan more than one column; the syntax

is similar to unique constraints:

<programlisting>

CREATE TABLE example (

</para>

<para>

A primary key indicates that a column or group of columns can be

used as a unique identifier for rows in the table. (This is a

direct consequence of the definition of a primary key. Note that

a unique constraint does not, by itself, provide a unique identifier

because it does not exclude null values.) This is useful both for

documentation purposes and for client applications. For example,

a GUI application that allows modifying row values probably needs

to know the primary key of a table to be able to identify rows

uniquely.

</para>

<para>

Adding a primary key will automatically create a unique btree index

on the column or group of columns used in the primary key.

Adding a primary key will automatically create a unique B-tree index

on the column or group of columns listed in the primary key, and will

force the column(s) to be marked <literal>NOT NULL</>.

</para>

<para>

A table can have at most one primary key. (There can be any number

of unique and not-null constraints, which are functionallythe same

thing, but only one can be identified as the primary key.)

of unique and not-null constraints, which are functionallyalmost the

samething, but only one can be identified as the primary key.)

Relational database theory

dictates that every table must have a primary key. This rule is

not enforced by <productname>PostgreSQL</productname>, but it is

usually best to follow it.

</para>

<para>

Primary keys are useful both for

documentation purposes and for client applications. For example,

a GUI application that allows modifying row values probably needs

to know the primary key of a table to be able to identify rows

uniquely. There are also various ways in which the database system

makes use of a primary key if one has been declared; for example,

the primary key defines the default target column(s) for foreign keys

referencing its table.

</para>

</sect2>

<sect2 id="ddl-constraints-fk">

<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>

<listitem>

<para>

The primary key constraint specifies that a column or columns of a table

can contain only unique (non-duplicate), nonnull values.

Technically, <literal>PRIMARY KEY</literal> is merely a

combination of <literal>UNIQUE</> and <literal>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.

The <literal>PRIMARY KEY</> constraint specifies that a column or

columns of a table can contain only unique (non-duplicate), nonnull

values. Only one primary key can be specified for a table, whether as a

column constraint or a table constraint.

</para>

<para>

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 the set of columns named by any unique

constraint defined for the same table. (Otherwise, the unique

constraint is redundant and will be discarded.)

</para>

<para>

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.

<literal>PRIMARY KEY</literal> enforces the same data constraints as

a combination of <literal>UNIQUE</> and <literal>NOT NULL</>, but

identifying a set of columns as the primary key also provides metadata

about the design of the schema, since a primary key implies that other

tables can rely on this set of columns as a unique identifier for rows.

</para>

</listitem>

</varlistentry>