To enforce uniqueness—and that includes a UNIQUE constraint or PRIMARY KEY constraints—your partitioning key must be included in the constraint itself if you want to use a local index to enforce the constraint.
This is the largest limitation of a local index, in my opinion. Oracle enforces uniqueness only within an index partition—never across partitions. What this implies, for example, is that you cannot range partition on a TIMESTAMP field and have a primary key on the ID that is enforced using a locally partitioned index. Oracle will instead utilize a global index to enforce uniqueness.

In the next example, we will create a range partitioned table that is partitioned by a column named TIMESTAMP but has a primary key on the ID column. We can do that by executing the following CREATE TABLE statement in a schema that owns no other objects, so we can easily see exactly what objects are created by looking at every segment this user owns:
SQL> CREATE TABLE partitioned( timestamp date,id int,constraint partitioned_pk primary key(id))
PARTITION BY RANGE (timestamp)
PARTITION part_1 VALUES LESS THAN
( to_date(’01/01/2021′,’dd/mm/yyyy’) ) , PARTITION part_2 VALUES LESS THAN
( to_date(’01/01/2022′,’dd/mm/yyyy’) ));Table created.

And inserting some data so that we get segments created:
SQL> insert into partitioned values(to_date(’01/01/2020′,’dd/mm/yyyy’),1); 1 row created.
SQL> insert into partitioned values(to_date(’01/01/2021′,’dd/mm/yyyy’),2); 1 row created.

Assuming we run this in a schema with no other objects created, we’ll see the following:
SQL > select segment_name, partition_name, segment_type from user_segments;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
PARTITIONED PART_1 TABLE PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED _PK INDEX

The PARTITIONED_PK index is not even partitioned, let alone locally partitioned, and as we’ll see, it cannot be locally partitioned. Even if we try to trick Oracle by realizing that a primary key can be enforced by a nonunique index as well as a unique index, we’ll find that this approach will not work either:
SQL> drop table partitioned;
SQL> CREATE TABLE partitioned( timestamp date,id int
PARTITION BY RANGE (timestamp)
PARTITION part_1 VALUES LESS THAN
( to_date(’01-jan-2021′,’dd-mon-yyyy’) ) , PARTITION part_2 VALUES LESS THAN
( to_date(’01-jan-2022′,’dd-mon-yyyy’) ));Table created.
SQL> create index partitioned_idx on partitioned(id) local; Index created.

And inserting some data so that we get segments created:
SQL> insert into partitioned values(to_date(’01/01/2020′,’dd/mm/yyyy’),1); 1 row created.
SQL> insert into partitioned values(to_date(’01/01/2021′,’dd/mm/yyyy’),2); 1 row created
SQL> select segment_name, partition_name, segment_type from user_segments;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
PARTITIONED PART_1 TABLE PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED_IDX PART_1 INDEX PARTITION
PARTITIONED_IDX PART_2 INDEX PARTITION

Here, Oracle attempts to create a global index on ID, but finds that it cannot since an index already exists. The preceding statements would work if the index we created was not partitioned, as Oracle would have used that index to enforce the constraint.

The reasons why uniqueness cannot be enforced, unless the partition key is part of the constraint, are twofold. First, if Oracle allowed this, it would void most of the advantages of partitions. Availability and scalability would be lost, as each and every partition would always have to be available and scanned to do any inserts and updates.
The more partitions you had, the less available the data would be. The more partitions you had, the more index partitions you would have to scan, and the less scalable partitions would become. Instead of providing availability and scalability, doing this would actually decrease both.

Additionally, Oracle would have to effectively serialize inserts and updates to this table at the transaction level. This is because if we add ID=1 to PART_1, Oracle would have to somehow prevent anyone else from adding ID=1 to PART_2. The only way to do this would be to prevent others from modifying index partition PART_2, since there isn’t anything to really lock in that partition.

In an OLTP system, unique constraints must be system enforced (i.e., enforced by Oracle) to ensure the integrity of data. This implies that the logical model of your application will have an impact on the physical design. Uniqueness constraints will either drive the underlying table partitioning scheme, driving the choice of the partition keys, or point you toward the use of global indexes instead. We’ll take a look at global indexes in more depth next.