Home » Developer & Programmer » Data Integration » Drop index
Drop index [message #261484] Wed, 22 August 2007 14:41 Go to next message
Messages: 8
Registered: July 2007
Location: US
Junior Member
Can any one explain me how to drop an index before running a map and create the index after importing. How can i use pre-mapping and post-mapping transformations.
My index is on primary key.

Re: Drop index [message #261530 is a reply to message #261484] Wed, 22 August 2007 23:42 Go to previous message
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I wouldn't know whether Warehouse Builder enables users to manipulate with constraints, so I'll show you how to do that using SQL*Plus.

First, I'll create a table with a primary key:
SQL> create table test (col_1 number constraint pk_test primary key);

Table created.
When the primary constraint is created, Oracle implicitly creates index associated with the constraint. So let's drop the index!
SQL> drop index pk_test;
drop index pk_test
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
Ooops! Can't do. OK, let's then drop constraint itself:
SQL> alter table test drop constraint pk_test;

Table altered.
Now, do whatever you meant to do (import, you've said?) and, once you are finished with it, recreate the constraint:
SQL> alter table test add constraint pk_test primary key (col_1);

Table altered.

I hope you understand that import will succeed regardless there are duplicate entries into the primary key column. But, if there really ARE duplicates, you will NOT be able to create primary key constraint on this column again.
Previous Topic: trouble importing
Next Topic: pivot procedure
Goto Forum:

Current Time: Sun May 26 03:11:45 CDT 2024