ORA-02429: cannot drop index used for enforcement of unique/primary key
========================================================================
---when I execute
SQL> drop index IND_ON_HALIM_TEST ; ----its reply
ORA-02429: cannot drop index used for enforcement of unique/primary key
If it is an unique index that enforces unique constraint
you cannot simply drop it
You have to drop unique constraint BY
alter table tablename drop constraint constraintname;
=================================================
Hands on Example
=================================================
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 30 16:21:01 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> CONN STLBAS/STLBAS@JUNCLS
Connected.
SQL>
SQL>
SQL> DROP TABLE HALIM_TEST ;
Table dropped.
SQL>
SQL> CREATE TABLE halim_test (id number,name varchar2(30)) ;
Table created.
SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr. Halim');
1 row created.
SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr Cadot');
1 row created.
SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (200,'Mr. Arif');
1 row created.
SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (300,'Mr. Akash');
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> create index IND_ON_HALIM_TEST on halim_test(ID);
Index created.
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;
Table altered.
SQL>
SQL>
SQL>
SQL> DROP index IND_ON_HALIM_TEST ;
DROP index IND_ON_HALIM_TEST
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL>
SQL>
SQL>
SQL>
SQL> SELECT owner, constraint_name, constraint_type, table_name
2 FROM user_constraints
3 WHERE table_name = 'HALIM_TEST';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------
STLBAS SYS_C0084077 P HALIM_TEST
SQL>
SQL>
SQL> alter table halim_test drop constraint SYS_C0084077 ;
Table altered.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT index_name, index_type, table_owner, table_name, table_type,
2 uniqueness
3 FROM user_indexes
4 WHERE table_name = 'HALIM_TEST';
INDEX_NAME INDEX_TYPE TABLE_OWNER
TABLE_NAME TABLE_TYPE UNIQUENES
------------------------------ --------------------------- ---------------------
--------- ------------------------------ ----------- ---------
IND_ON_HALIM_TEST NORMAL STLBAS
HALIM_TEST TABLE NONUNIQUE
SQL>
SQL>
SQL> drop index IND_ON_HALIM_TEST;
Index dropped.
SQL>
SQL>
SQL>
SQL>
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
4096 Columns1 week ago
-
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment