RE: constraint and index creation

From: D'Hooge Freek <>
Date: Fri, 11 Nov 2011 08:55:08 +0100
Message-ID: <>


The first example was to show that Oracle is considering the index created in the "using index" clause as an implicit index.

The second example was to show that if the constraint is not explicitly named, the name of the index created using the "using index" clause is considered as a system generated name, despite the fact that you did name it.

Only an index that is created separately from the constraint creation is considered as an explicit index.

>> Also when dropping the constraint the index supporting it will also be dropped by default (if it >>is an unique index).
>-- unless you use KEEP INDEX clause.

True, that's why I said: "by default" and "(if it is an unique index)" It is actually not a bad idea to specify "keep index" or "drop index" every time to guarantee a consistent behaviour.


Freek D'Hooge
Oracle Database Administrator
tel +32(0)3 451 23 82

Received on Fri Nov 11 2011 - 01:55:08 CST

Original text of this message