As part my project's development process, I am developing two procedures, one for creating index and the other for dropping it. In our DB, we already have a table with primary key constraint on a set of columns, say (a,b,c). I created the procedures. not a great work. very simple, right?, just creating index. I've done that. Now, time to test them. I started my testing with DROP procedure. As per my guess it should fail with error message like no index exists... Yes it failed, but error is different. Some thing like cannot drop index used for enforcement of unique/primary key. STRANGE, never saw such message. I had a marathon on............ on what?........wht else, on Google. I collected some information regarding this problem. Thanks to Tom again. Here I tried to explain the things in a stupid way, I have a table student(sid, sname) Added PK constraint alter table student add constraint pk_student primary key(sid); Create Unique Index ro achieve performance create unique index idx_student on student(sid); now if I try to drop the index... drop index idx_student ORA-02429: cannot drop index used for enforcement of unique/primary key Solution: first drop or disable the constraint and then drop index Same point, trying to put more in detail... If I create a table with primary or uniuqe constraint then it will automatically create a Unique index but nor vice versa. SQL> CREATE TABLE IDX_TABLE(IDNO NUMBER (4)); Table created. After inserting values... SQL> SELECT * FROM IDX_TABLE; IDNO ---------- 11 12 13 14 15 creating primary key constraint... SQL> ALTER TABLE IDX_TABLE ADD CONSTRAINT PK_IDX_TABLE PRIMARY KEY(IDNO); Table altered. SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'IDX_TABLE'; CONSTRAINT_NAME ------------------------------ PK_IDX_TABLE SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'IDX_TABLE'; INDEX_NAME ------------------------------ PK_IDX_TABLE So, if I try to create index on IDX_TABLE on the same set of columns it will throw an error. SQL> DROP INDEX PK_IDX_TABLE; DROP INDEX PK_IDX_TABLE * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key When u try to create a Unique or Primary Key constraint...

• if index is not found(yes, not found here) and constraint is not deferrable(yes, not deferrable here), it creates unique index by default(so, created unique index)
• if not found(yes, not found here) and constraint is deferrable(NO, not deferrable here), it creates non-unique index by default

Dropping Constraint SQL> ALTER TABLE IDX_TABLE DROP CONSTRAINT PK_IDX_TABLE; Table altered. Now Creating Index(this will not create constraint) SQL> CREATE UNIQUE INDEX UNIQ_IDX_TABLE ON IDX_TABLE(IDNO); Index created. SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'IDX_TABLE'; INDEX_NAME ------------------------------ UNIQ_IDX_TABLE SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'IDX_TABLE'; no rows selected - though there is no constraint on IDX_TABLE, but it still validates the data, as Unique Index is already created on it. Now, creating constraint SQL> ALTER TABLE IDX_TABLE ADD CONSTRAINT PK_IDX_TABLE PRIMARY KEY(IDNO); Table altered. SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'IDX_TABLE'; INDEX_NAME ------------------------------ UNIQ_IDX_TABLE SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'IDX_TABLE'; CONSTRAINT_NAME ------------------------------ PK_IDX_TABLE Hope I am able to clear the point. Oracle recommends to create Index explicitly(using CREATE INDEX...) rather depending on Unique or Primary Key constraint. While roaming through Tom's site I got a link to a resource on Oracle Index Internals. I haven't gone through it yet, but Tom has given a good feedback on it. I am thinking of going through it. If I find anything interesting from this article, definetely I will have a post on it. Here is the link http://www.actoug.org.au/Downloads/oracle_index_internals.pdf Karteek