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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016