Why Does The Primary Key Constraint Point To A Different Index After Import? (Doc ID 1455492.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 19-Nov-2015***

Goal

You started DataPump export/import to transfer a table with indexes and constraints to another database. After import, you observed that the primary key constraint is using a different index than the originally defined one.

The next test demonstrates this:


=> Now the PK constraint with system generated name SYS_C006210 uses the non unique index A_IND.

Explanation

Export puts the DDLs into the dump in the following order:

1. CREATE TABLE statement
2. CREATE INDEX statements (see <bug 6156708>, this must be fixed in source database before running the export)
3. ALTER TABLE ADD CONSTRAINT statements

During import, this sequence order is kept. When adding a primary/unique key constraint (step 3), Oracle checks if any existing index can be utilized to enforce the constraint, and in that case, uses it instead of creating a unique index (which was done before in 7.x releases unconditionally). This was done to minimize disk utilization and time needed for the constraint to be enabled.

In the example above, the primary key constraint has a system generated name. So the adjacent CREATE UNIQUE INDEX statement (with same system generated name) will be missing from dump file (step 2).

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms