Constraint changes

There are three ways to add a unique or primary key constraint in Oracle:

1) create the constraint (let Oracle create the unique index automatically)
2) create a unique index first, then add the constraint
3) create a non-unique index first, then add the constraint

For (1) and (2), in Oracle 9, if you disable the constraint, the unique index is also dropped. For example,

      SQL> create table T ( x number);

      Table created.

      SQL> create unique index U on T ( x );

      Index created.

      SQL> alter table T add constraint C primary key ( x );

      Table altered.

      SQL> alter table T modify constraint C disable;

      Table altered.

      SQL> select index_name from user_indexes;

      no rows selected


In Oracle 10, things have changed.  Only if the index was created automatically by Oracle, will it be dropped.
      SQL> create table T ( x number);

      Table created.

      SQL> create unique index U on T ( x );

      Index created.

      SQL> alter table T add constraint C primary key ( x );

      Table altered.

      SQL> alter table T modify constraint C disable;

      Table altered.

      SQL> select index_name from user_indexes;

      INDEX_NAME
      ------------------------------
      U