When adding partitioning keys in partitioning wizard, encounters error message : The columns of a local unique index have to contain the partitioning key columns. The columns of the index <SCHEMA>.SYS_C0010300 do not contain all the partitioning key columns.
It is an Oracle limitation. The partitioning key does NOT need to be part of the primary key index columns, however the partitioning key columns must be included in any LOCAL UNIQUE index.
Re-evaluate the column to use for partitioning key or re-evaluate the column to use for local unique index.
Can be attempt in SQL*Plus
-- this WILL CAUSE AN ERROR - there is a UNIQUE LOCAL index that does not contain the partitioning key
create table customer (customer_id number, customer_name varchar2 (30),
legacy_id number,
constraint customer_pk primary key (customer_id) using index LOCAL)
partition by range (customer_name)
(partition p1 values less than (S),
partition p2 values less than (maxvalue)
) ;
-- this will NOT cause an error: the unique index for the constraint is GLOBAL
create table customer (customer_id number, customer_name varchar2 (30),
legacy_id number,
constraint customer_pk primary key (customer_id) using index)
partition by range (customer_name)
(partition p1 values less than (S),
partition p2 values less than (maxvalue)
) ;
