Re: Is index used for constraint enforcement?

> Oracle may but you should never allow it. Primary key and unique
> constraints should always be built with the USING INDEX clause forcing
> constraint name and index name to be identical.

USING INDEX clause doesn't force index creation. If an usable index is already existing the pk constraint will use this one instead of creating a new.

Example:

SQL> create table foo(x int);

Table created.

SQL> create index u1 on foo(x);

Index created.

SQL> alter table foo add primary key (x) using index;

Table altered.

The pk will be based on u1. No additional index is created.

SQL> select index_name from user_indexes;

INDEX_NAME

SQL> select constraint_name from user_constraints;

CONSTRAINT_NAME