My Oracle Support Banner

Primary Key Constraint and its Index's Names Are Not Same For Master Table and MVIEW. (Doc ID 2049847.1)

Last updated on FEBRUARY 28, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Goal

The name of the Primary Key Constraint and its Index is not same between Master Table and MVIEW.

Consider a master table TEST created as:
create table test ( c1 varchar2(100), CONSTRAINT TEST_PK primary key(c1) USING INDEX );

Create a Materialized view log as:
CREATE MATERIALIZED VIEW LOG ON TEST WITH PRIMARY KEY;

The following are the names of the PK constraint and its index of the Master Table:
Constraint Name: TEST_PK
Index Name: TEST_PK

Now, Create the MVIEW as:

CREATE MATERIALIZED VIEW TEST_MV (C1)
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS SELECT C1 FROM TEST;

The following are the names of the PK constraint and its index of the MVIEW:
Constraint Name: SYS_C_SNAP$_501TEST_PK
Index Name: SYS_C_SNAP$_501TEST_PK

The question is:
Why the constraint name & index name of the MVIEW differs from the Master Table?

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.