Oracle - Drop an Index in Oracle

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.

⇒Managing Oracle Table Indexes

⇒⇒Oracle Database Tutorials

2019-04-22, 642👍, 0💬

Error: Cannot Drop Index on Primary Key in Oracle
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not delete the index associated with a unique or primary key constraint. If you try, you will get an error like this: ORA-02429: cannot drop index used for enforcement of unique/primary key. ⇒Managing Oracl... 2019-04-22, 664👍, 0💬

Drop an Index in Oracle
How To Drop an Index in Oracle? 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, ... 2019-04-22, 643👍, 0💬

Dropped Tables with Indexes in Oracle
What Happens to Indexes If You Drop a Table in Oracle? If you drop a table, what happens to its indexes? The answer is that if a table is dropped, all its indexes will be dropped too. Try the following script to see yourself: CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT... 2019-04-22, 634👍, 0💬

Indexes for the PRIMARY KEY Column in Oracle
What Is an Index Associated with a Constraint in Oracle? An index associated with a constraint because this constraint is required to have an index. There are two types of constraints are required to have indexes: UNIQUE and PRIMARY KEY. When you defines a UNIQUE or PRIMARY KEY constraint in a table... 2019-05-01, 586👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.