Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Dropped Tables with Indexes

By: FYIcenter.com

(Continued from previous topic...)

What Happens to Indexes If You Drop a Table?

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 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_C004141             STUDENT               UNIQUE
SYS_C004142             STUDENT               UNIQUE
STUDENT_BIRTH_DATE      STUDENT               NONUNIQUE 

DROP TABLE STUDENT;
Table dropped.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
no data found

No records in USER_INDEXES view found for your name STUDENT, after you dropped STUDENT table.

(Continued on next topic...)

1. What Is an Index?
2. How To Run SQL Statements through the Web Interface?
3. How To Create a Table Index?
4. How To List All Indexes in Your Schema?
5. What Is an Index Associated with a Constraint?
6. How To Rename an Index?
7. How To Drop an Index?
8. Can You Drop an Index Associated with a Unique or Primary Key Constraint?
9. What Happens to Indexes If You Drop a Table?
10. How To Recover a Dropped Index?
11. What Happens to the Indexes If a Table Is Recovered?
12. How To Rebuild an Index?
13. How To See the Table Columns Used in an Index?
14. How To Create a Single Index for Multiple Columns?