19.5.1 Partitioning Keys, Primary Keys, and Unique Keys

This section discusses the relationship of partitioning keys
with primary keys and unique keys. The rule governing this
relationship can be expressed as follows: All columns used in
the partitioning expression for a partitioned table must be part
of every unique key that the table may have.

In other words, every unique key on the table must use
every column in the table's partitioning expression.
(This also includes the table's primary key, since it is by
definition a unique key. This particular case is discussed later
in this section.) For example, each of the following table
creation statements is invalid:

The CREATE TABLE statement fails
because both col1 and col3
are included in the proposed partitioning key, but neither of
these columns is part of both of unique keys on the table. This
shows one possible fix for the invalid table definition:

If a table has no unique keys—this includes having no
primary key—then this restriction does not apply, and you
may use any column or columns in the partitioning expression as
long as the column type is compatible with the partitioning
type.

For the same reason, you cannot later add a unique key to a
partitioned table unless the key includes all columns used by
the table's partitioning expression. Consider the partitioned
table created as shown here:

In the case of np_pk, the only column that
may be used as part of a partitioning expression is
id; if you wish to partition this table using
any other column or columns in the partitioning expression, you
must first modify the table, either by adding the desired column
or columns to the primary key, or by dropping the primary key
altogether.