Databases Reference
In-Depth Information
Here is the output for this example:
INDEX_NAME INDEX_TYPE UNIQUENES
-------------------- --------------- ---------
CUST_PK NORMAL UNIQUE
Here is a query to verify the constraint information:
select constraint_name, constraint_type
from user_constraints
where table_name = 'CUST';
Here is the corresponding output:
CONSTRAINT_NAME CONSTRAINT_TYPE
-------------------- ---------------
CUST_PK P
Dropping Primary Key Constraint and Index
An index that was automatically created (when the primary key constraint was created) can't be directly
dropped. In this scenario, if you attempt to drop the index
SQL> drop index cust_pk;
you'll receive this error
ORA-02429: cannot drop index used for enforcement of unique/primary key
To remove the index, you must do so by dropping or disabling the primary key constraint. For
example, any of the following statements will drop an index that was automatically created when the
constraint was created:
SQL> alter table cust disable constraint cust_pk;
SQL> alter table cust drop constraint cust_pk;
SQL> alter table cust drop primary key;
When you drop or disable a primary key constraint, you have the option of not dropping the
associated index. Use the
KEEP INDEX
clause of the
DROP/DISABLE CONSTRAINT
clause to retain the index.
For example,
SQL> alter table cust drop constraint cust_pk keep index;
This code instructs Oracle to drop the constraint but keep the index. If you're working with large
tables, you may want to disable or drop the constraint for performance reasons while loading or
manipulating the data. Dropping the index associated with a large table may take considerable time and
resources to re-create.
One other aspect to be aware of is that if a primary key or unique key is referenced by an enabled
foreign key, and you attempt to drop the constraint on the parent table
