New Oracle9i named indexes on constraints   Donald K. Burleson

As we may know, Oracle provides referential integrity via constraints.  The Oracle DBA can define primary key and foreign key constraints, with either the ON DELETE CASCADE or ON DELETE NO ACTION clauses, thereby enforcing business rules at the database level.

However, creating indexes on constraints has been somewhat problematic because Oracle would assign a cryptic internal name to the indexes that support all unique constraints.  Internally, Oracle enforces PRIMARY and UNIQUE constraints by building a unique index on the specified column.  This ensures that no duplicate rows are added to the column.

In Oracle9i, the index used to support primary and unique keys can be defined independently of the constraint itself by using the CREATE INDEX syntax within the USING INDEX clause of the CREATE TABLE statement:

CREATE TABLE

   student
(
  student_id           NUMBER(6),
  student_name     VARCHAR2(30),
  dept_no               NUMBER(2),
  CONSTRAINT student_pk primary key(student_id)
    USING INDEX
    (CREATE INDEX student_pk_idx ON employee(student_id))
);

Once defined, the unique constraint can be dropped without dropping the index. 

ALTER TABLE student DROP PRIMARY KEY KEEP INDEX;

ALTER TABLE student DROP CONSTRAINT student_pk;

This adds additional flexibility to referential integrity and allows the Oracle9i DBA to manage indexes independently of constraints.

If you like Oracle tuning, check out my latest book "Oracle Tuning: The Definitive Reference".  It's 980 pages of hard-core tuning insights, tips and scripts, and you can buy it direct from the publisher for 30%-off. Best of all, you get instant access to the code depot of Oracle tuning scripts.