Monday, December 19, 2011

Can we set Index for primary key? If so what is the name of the Index?


Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration. For example, if you create a table foo as follows:

create table foo (a int primary key,
                          b varchar(20) unique);

Oracle will automatically create one index on foo.a and another on foo.b. Note that you cannot drop indexes for UNIQUE and PRIMARY KEY attributes. These indexes are dropped automatically when you drop the table or the key constraints (see the section on Constraints).

To find out what indexes you have, use
    select index_name from user_indexes;

USER_INDEXES is another system table just like USER_TABLES. This can become especially helpful if you forget the names of your indexes and therefore cannot drop them. You might also see weird names of the indexes created by Oracle for UNIQUE and PRIMARY KEY attributes, but you will not be able to drop these indexes.

1 comment: