SQL Extensions   «Prev  Next»

Lesson 8TABLE, DROP, or DISABLE CONSTRAINT commands
ObjectiveDescribe the effects of dropping a table and disabling or removing a constraint.

πŸ”½ Oracle 23ai: DROP TABLE Syntax

βœ… The theory and syntax for dropping tables using `DROP TABLE ... [CASCADE CONSTRAINTS]` still applies to Oracle 23ai.
DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE];

πŸ”Ή Explanation of Keywords:
  • DROP TABLE: Deletes the table definition and all associated data.
  • CASCADE CONSTRAINTS:
    • Required if other tables reference this table via foreign keys.
    • Drops the dependent foreign key constraints automatically.
  • PURGE (Optional but new in newer versions):
    • Permanently removes the table without placing it in the Recycle Bin.


DROP TABLE CUSTOMER CASCADE CONSTRAINTS;

  • Drops the CUSTOMER table.
  • Automatically drops foreign key constraints in referencing tables like CUSTOMER_SALE.

πŸ†• Oracle 23ai Enhancements (Related)
  • Oracle 23ai continues to support declarative DDL, and the CASCADE CONSTRAINTS clause works just like in 11g, 12c, 19c, and 21c.
  • Optional use of the PURGE keyword can bypass the Recycle Bin:
DROP TABLE CUSTOMER CASCADE CONSTRAINTS PURGE;

This is useful in secure or automated environments where you want the object fully removed without leaving metadata behind.
⚠️ Best Practice: * Always identify dependent objects before dropping, using:
  SELECT * FROM USER_DEPENDENCIES WHERE NAME = 'CUSTOMER';
  

TABLE, DROP, or DISABLE CONSTRAINT Commands

The final lesson in this module looks at removing tables and constraints from an existing database. The syntax of these commands is quite simple. Let's look at dropping a table first.
The syntax is as follows:
DROP TABLE tablename [CASCADE CONSTRAINTS]

When you specify CASCADE CONSTRAINTS, any foreign key constraints that reference the table being dropped are also dropped. The effects of dropping a table are not as simple as the command syntax. Take a look at this Slide Show to see what happens.

Here is a diagram showing the CUSTOMER table and all the related objects in the database
πŸ“Š Diagram Elements (Related to `CUSTOMER` Table)
🧾 Primary Table:
CUSTOMER
  • CUST_ID: Primary key
  • Stores name and address of customers

πŸ“‘ Objects Related to `CUSTOMER` Table:
  1. Index
    • Created on the STATE column
  2. Privileges
    • Granted on the CUSTOMER table
  3. Foreign Key Constraint
    • Present in the CUSTOMER_SALE table referencing CUSTOMER
  4. Dependent Table
    • CUSTOMER_SALE
      • Has SALES_ID
      • Contains retail sale data to a customer
  5. View
    • CUSTOMER_VIEW: A view based on the CUSTOMER table
  6. Synonym
    • CUSTOMER_PUBLIC: A public synonym for the CUSTOMER table

1) Here is a diagram showing the CUSTOMER table and all the related objects in the database. The following elements exist: 1) index, 2) privileges, 3) a view, 4) a foreign key in a another table, 5) a view , and a 6) synonym. What happens to all these objects if we drop the CUSTOMER table?
πŸ” Explanation: What Happens When You DROP the `CUSTOMER` Table?
🧨 If you use:
DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
  • βœ… Will drop the table and any foreign key constraints pointing to it
  • ❌ Will not drop the dependent view or synonym β€” they remain but become invalid

Some of the objects are dropped along with the table while other objects are left in the database
This diagram shows the effects of dropping the `CUSTOMER` table in Oracle:
πŸ“Š Diagram Elements: Status After Dropping the `CUSTOMER` Table
❌ Dropped Objects:
  1. CUSTOMER Table
    • Including the CUST_ID primary key
  2. Index on STATE Column
    • Dropped automatically with the table
  3. Privileges Granted on CUSTOMER
    • Dropped automatically
  4. Foreign Key Constraint in CUSTOMER_SALE
    • Dropped; leaves behind invalid reference

⚠️ Remaining But Invalid Objects:
  1. CUSTOMER_VIEW
    • View of the CUSTOMER table
    • Invalid because the base table no longer exists
  2. CUSTOMER_PUBLIC
    • Public synonym of the CUSTOMER table
    • Invalid due to broken reference

⚠️ Data Integrity Issue:
CUSTOMER_SALE
  • Still exists
  • Contains CUST_ID values with no point of reference
  • Now holds invalid data

πŸ“œText from the Image As illustrated here, some of the objects are dropped along with the table while other objects are left in the database. Those objects that remain will cause errors if they are used at any time after this point. They must either be either dropped or revised.
βœ… Summary
Object Status Notes
CUSTOMER ❌ Dropped Main table removed
Index on STATE ❌ Dropped Auto-dropped
Privileges ❌ Dropped Auto-revoked
Foreign Key in CUSTOMER_SALE ❌ Dropped Integrity lost
CUSTOMER_SALE ⚠️ Exists, invalid data CUST_ID now orphaned
CUSTOMER_VIEW ❌ Invalid Needs to be dropped or redefined
CUSTOMER_PUBLIC ❌ Invalid Synonym broken

2) As illustrated here, some of the objects are dropped along with the table while other objects are left in the database. These objects that remain will cause errors if they are used at any time after this point. They must either be either dropped or revised.

  1. Here is a diagram showing the CUSTOMER table and all the related objects in the database.
  2. As illustrated here, some of the objects are dropped along with the table while other objects are left in the database.

Whenever you prepare to drop a table, make sure that you know which related objects, such as indexes, views, and foreign keys, will be affected.


Dropping Constraints

Dropping a constraint is done using the ALTER TABLE command as follows:
ALTER TABLE tablename DROP CONSTRAINT constraint_name

For example, if the constraint for the primary key of the PET_STORE table is named PET_STORE_PK, then the command to drop the primary key constraint is:
ALTER TABLE PET_STORE 
DROP CONSTRAINT PET_STORE_PK

You might drop a constraint if you want to replace it with a different constraint, or if the constraint is causing problems while adding data to the database. For example, if you have a CHECK constraint that has a list of values that is not complete, you might want to drop and recreate that constraint.

Disabling Constraints

There are times, especially during development, when you want to be able to ignore a constraint without actually removing it. For example, let's say that you have a parent table called ORDERS and a child table called ORDER_DETAILS. During the test phase, you want to delete and reload all the data in the ORDERS table while leaving the ORDER_DETAILS table intact. The foreign key on the ORDER_DETAILS table prevents you from deleting a parent row that has children. The best way to handle this is to disable the FOREIGN KEY constraint temporarily while you delete and reload the parent table. The syntax for this is:
ALTER TABLE tablename DISABLE CONSTRAINT
constraint_name
In the example described, the code might look like this:
ALTER TABLE ORDER_DETAILS DISABLE CONSTRAINT
ORDER_PARENT

Once the data has been restored, issue this command to reinstate the constraint:
ALTER TABLE ORDER_DETAILS ENABLE CONSTRAINT
ORDER_PARENT
The next lesson concludes this module.

SEMrush Software