Databases Reference
In-Depth Information
If you have created your
UNIQUE INDEX
first, and then enabled your primary key constraint, you can
see in the following example that your constraint and index still exist in the database:
SQL> alter table employees_part disable constraint employees_part_pk;
Table altered.
SQL> select i.index_name, c.constraint_type, i.partitioned
2 from user_indexes i left join user_constraints c
3 on (i.index_name = c.constraint_name)
4 where i.index_name = 'EMPLOYEES_PART_PK';
INDEX_NAME C PAR
------------------------------ - ---
EMPLOYEES_PART_PK P YES
If you created your primary key inline with the
CREATE TABLE
statement or a single
ALTER TABLE
statement, this will create the underlying unique index. If you disable your primary key constraint in this
case, you can see it drops the underlying index.
SQL> alter table employees_part disable constraint employees_part_pk;
Table altered.
SQL> select i.index_name, c.constraint_type, i.partitioned
2 from user_indexes i left join user_constraints c
3 on (i.index_name = c.constraint_name)
4 where i.index_name = 'EMPLOYEES_PART_PK';
no rows selected
When this becomes very useful with locally partitioned indexes is when you need to perform
partition-level operations on your table, which would render the index for a partition
UNUSABLE
—such as
performing a partition split on a table. In the case of your primary key, you could simply disable the
primary key constraint (in which case the underlying index would remain intact), perform your
partition-level operation, rebuild the index(es) for that partition, and then re-enable the primary key
constraint. In this case, the only part of the index that would be rebuilt is the partition(s) affected by the
operation.
Another common issue with the limitation of having the partitioning column as part of a unique
index, which in turns becomes the primary key, is that sometimes the user requirements are such that
the partitioning column is not desired as one of the primary key columns on a table. As shown in the
following example, you can simply alter the
EMPLOYEES
table to create a primary key constraint using the
unique index created previously:
SQL> alter table employees_part add constraint employees_part_pk
2 primary key (employee_id);
Table altered.
