Search My Oracle Blog

Custom Search

05 April, 2012

Primary Key name appears to be different

Here is a quick post on a scenario where Oracle attempts to validate a Primary Key (index present) when creating a Materialized View and apparently reports a different name for the constraint !. The Primary Key and Indexis called "TARGET_TABLE" but the ORA-2437 error reports the error as being against "TARGET_TABLE1" -- with a "1" seemingly appended.


SQL> drop table target_table purge;

Table dropped.

SQL> create table target_table (column_1 number not null, column_2 varchar2(30), column_3 varchar2(300));

Table created.

SQL> 
SQL> insert into target_table
  2  select rownum, dbms_random.string('X',8),dbms_random.string('U',30)
  3  from dual connect by level < 101;

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from target_table;

  COUNT(*)
----------
       100

SQL> 
SQL> create index target_table on target_table(column_1);

Index created.

SQL> insert into target_table values (5,'ABC','XYZXZYZ');

1 row created.

SQL> 
SQL> alter table target_table add constraint target_table primary key (column_1) enable novalidate ;

Table altered.

SQL> 
SQL> select index_name from user_indexes where table_name = 'TARGET_TABLE';

INDEX_NAME
------------------------------
TARGET_TABLE

SQL> 
SQL> select constraint_name, index_name
  2  from user_constraints
  3  where table_name = 'TARGET_TABLE'
  4  and constraint_type in ('U','P');

CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------
TARGET_TABLE                   TARGET_TABLE

SQL> 
SQL> create materialized view log on target_table;

Materialized view log created.

SQL> 
SQL> create materialized view tt_mv
  2  refresh fast on demand
  3  as select column_1, column_2
  4  from target_table
  5  /
from target_table
     *
ERROR at line 4:
ORA-02437: cannot validate (HEMANT.TARGET_TABLE1) - primary key violated


SQL> 
SQL> 
SQL> select count(*) from target_table where column_1 = 5;

  COUNT(*)
----------
         2

SQL> 
Today's question : Can you create another test case where a "1" appears to be appended ?
.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com