General MySQL Oracle SQL Server

Drop an Index in Oracle

Q

How To Drop an Index in Oracle?

✍: FYIcenter.com

A

If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.

CREATE INDEX student_birth_date ON student(birth_date);
Index created.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004129             STUDENT               UNIQUE
SYS_C004130             STUDENT               UNIQUE
STUDENT_BIRTH_DATE      STUDENT               NONUNIQUE 

DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.

 

⇒ Error: Cannot Drop Index on Primary Key in Oracle

⇐ Rename an Index in Oracle

⇑ Managing Oracle Table Indexes

⇑⇑ Oracle Database Tutorials

2019-04-22, 4649🔥, 0💬

General MySQL Oracle SQL Server