Dropping Tables and Managing Constraints in Oracle 26ai

Understand the DROP TABLE alternatives

The simplest form names the table to remove. If another table has a foreign key that references a primary or unique key in CUSTOMER, however, the statement fails. Oracle does not partially remove the table or its dependencies:

DROP TABLE customer;

CASCADE CONSTRAINTS authorizes Oracle to remove foreign keys in other tables that reference keys in the target table. In Oracle AI Database 26ai, it also removes SQL assertions that reference that table. It does not delete the child tables or their rows, and it is unrelated to a foreign key's ON DELETE CASCADE row behavior.

DROP TABLE customer CASCADE CONSTRAINTS;

Without PURGE, a normal drop uses the recycle bin when that feature is enabled. The table is removed from its active schema namespace, renamed to a system-generated BIN$... name, and normally remains recoverable. Adding PURGE bypasses that recovery path and releases the associated space immediately:

DROP TABLE customer CASCADE CONSTRAINTS PURGE;

Current DDL also supports IF EXISTS. It suppresses the missing-table error, which can simplify repeatable deployment or cleanup scripts. It does not check dependencies, preserve data, or make a purged object recoverable. IF NOT EXISTS is not valid with DROP TABLE.

DROP TABLE IF EXISTS staging_customer PURGE;

Ordinary DDL implicitly commits the current transaction before execution and commits the DDL when it succeeds. A successful drop cannot be reversed with ROLLBACK. Private temporary tables are a special exception to the ordinary commit and recycle-bin behavior, while an external-table drop removes database metadata rather than the external files. Specialized table types have additional restrictions and should be checked in the current SQL Language Reference before production use.

Choose DROP only when the table should cease to exist

DROP TABLE is an object-lifecycle operation, not merely a fast way to remove rows. It changes the schema namespace, removes grants, affects dependent objects, and can eliminate external referential constraints. Use it when the table definition itself is being retired or replaced and those consequences are part of the approved change.

If the requirement is to remove selected rows while retaining the table, DELETE is the natural DML operation. Its changes participate in the transaction and remain subject to triggers, foreign keys, and other integrity rules. If the requirement is to empty an entire eligible table while retaining its definition, grants, constraints, and triggers, TRUNCATE TABLE may be appropriate. Truncation is DDL, has its own referential restrictions, and cannot be treated as a rollback-capable substitute for DELETE.

A data refresh therefore does not automatically justify dropping and recreating the table. Recreation can require grants, indexes, external foreign keys, triggers, statistics, synonyms, dependent objects, and application assumptions to be repaired. Choose among deletion, truncation, staging, exchange, redefinition, and dropping according to whether rows, storage, or the schema object is actually being replaced.

Map the CUSTOMER dependencies before dropping

The first diagram models the objects that matter to the example. CUSTOMER.CUST_ID is the primary key, and CUSTOMER.STATE has an index. Object privileges have been granted on the table. CUSTOMER_SALE.CUST_ID is a foreign key that references the customer key, CUSTOMER_VIEW selects from the table, and CUSTOMER_PUBLIC is a public synonym for it.

The foreign-key relationship is not merely an application convention. Oracle stores it as a constraint that identifies the referenced primary or unique constraint. The following query joins the child constraint to that parent key and reports the enforcement and validation state:

SELECT child.table_name,
       child.constraint_name,
       child.status,
       child.validated,
       parent.constraint_name AS referenced_key
FROM   user_constraints child
JOIN   user_constraints parent
       ON parent.constraint_name = child.r_constraint_name
      AND parent.owner = child.r_owner
WHERE  child.constraint_type = 'R'
AND    parent.table_name = 'CUSTOMER'
ORDER  BY child.table_name, child.constraint_name;

A disabled foreign key does not automatically make the parent safe to drop. Even a foreign key in DISABLE NOVALIDATE state still prevents dropping the referenced primary or unique key unless the required cascade clause is used. Inventory the definition, not only whether current DML enforcement is enabled.

Views and stored program units are described through dependency metadata rather than referential constraints:

SELECT name,
       type,
       referenced_name,
       referenced_type
FROM   user_dependencies
WHERE  referenced_name = 'CUSTOMER'
ORDER  BY type, name;

No single view gives a complete impact report. Synonyms require USER_SYNONYMS or an authorized broader view; grants can be checked in USER_TAB_PRIVS_MADE; and indexes, triggers, materialized-view logs, assertions, replication, application SQL, and external integrations require their own inspection. Capture the complete table DDL and related definitions before issuing destructive DDL.

Follow the successful CASCADE CONSTRAINTS path

Assume that the inventory is complete, the external foreign key is intentionally being removed, and recovery requirements allow the table to be dropped. The following statement succeeds because it authorizes removal of the referencing constraint:

DROP TABLE customer CASCADE CONSTRAINTS;

The rows in CUSTOMER_SALE are not marked invalid by Oracle. Their CUST_ID values may be orphaned because no remaining foreign key requires a matching parent. The child table can still be queried and changed according to its other constraints and privileges. Re-creating CUSTOMER with the same name does not automatically recreate that foreign key or restore the removed grants.

Verify objects that remain

The view and public synonym require different checks because their post-drop states are different. The view is a compiled dependent object whose status becomes invalid. The synonym remains a name mapping; it can still be listed even though resolving its target fails. An authorized account can inspect both definitions as follows:

SELECT object_name,
       object_type,
       status
FROM   user_objects
WHERE  object_name = 'CUSTOMER_VIEW';

SELECT owner,
       synonym_name,
       table_owner,
       table_name
FROM   all_synonyms
WHERE  owner = 'PUBLIC'
AND    synonym_name = 'CUSTOMER_PUBLIC';

Do not interpret the synonym's presence as proof that its target exists. Test resolution only after the table and any required privileges have been restored. Likewise, recompiling a view cannot repair a missing or incompatible base table; the dependency must first be made valid.

Verify the recycle-bin result

With the recycle bin enabled, the dropped table and eligible associated objects continue to occupy space and count against the user's quota. Oracle can later reclaim recycled objects under space pressure, so the recycle bin is a recovery convenience rather than a long-term backup. Query it promptly when a drop must be investigated:

SELECT object_name,
       original_name,
       type,
       droptime
FROM   user_recyclebin
WHERE  original_name = 'CUSTOMER'
ORDER  BY droptime DESC;

An eligible table that has not been purged can be recovered with Flashback Drop:

FLASHBACK TABLE customer TO BEFORE DROP;

Recovered indexes, triggers, and constraints can retain their system-generated recycle-bin names and may need to be renamed. External foreign keys removed by CASCADE CONSTRAINTS and object privileges removed by the drop are not restored merely by flashing back the table. Verify and recreate those definitions deliberately. A view can become valid again when a compatible target returns, and a synonym can resolve again, but both still require testing.

Use the purged form only when immediate and irreversible removal from the recycle-bin path is intended:

DROP TABLE customer CASCADE CONSTRAINTS PURGE;

PURGE is not a promise that every backup, archived redo record, audit record, or storage copy has been securely erased. Its documented effect here is to bypass the database recycle bin, release the associated space, and prevent recovery with FLASHBACK TABLE ... TO BEFORE DROP.

Drop a constraint without dropping its table

A named constraint can be removed independently with ALTER TABLE. The table and rows remain, but Oracle no longer stores or enforces that rule. The following statement removes the foreign key named ORDER_PARENT from ORDER_DETAILS:

ALTER TABLE order_details
DROP CONSTRAINT order_parent;

If a primary or unique constraint is referenced by foreign keys, dropping it requires the separate CASCADE keyword. This clause removes the dependent foreign keys; it does not delete their tables or rows:

ALTER TABLE orders
DROP CONSTRAINT orders_pk CASCADE;

This grammar is intentionally different from DROP TABLE ... CASCADE CONSTRAINTS. For a primary or unique constraint, Oracle can also remove the unique index it created for enforcement. KEEP INDEX can preserve an eligible supporting index when that matches the migration plan. A foreign key normally does not cause Oracle to create an index automatically, so do not assume every constraint owns a disposable index.

Drop a constraint when the business rule is being retired or replaced, not simply because inconvenient data violates it. Before removal, determine which foreign keys reference it, whether an index supports it, which applications depend on the rule, and whether a replacement will be installed during the same controlled change.

Distinguish enforcement from validation

Disabling a constraint retains its definition in the dictionary. Writing DISABLE without a validation keyword defaults to DISABLE NOVALIDATE: Oracle stops enforcing the rule and does not guarantee that the stored rows satisfy it.

ALTER TABLE order_details
DISABLE NOVALIDATE CONSTRAINT order_parent;

DISABLE VALIDATE is a specialized data-warehouse state, not a general solution for development reloads. It can drop a unique enforcing index while retaining validated metadata, but ordinary table modifications are disallowed. Similarly, disabling a primary or unique constraint can drop its unique index unless the design preserves a suitable index strategy.

If only the order of operations inside one transaction must be postponed, a constraint originally defined as DEFERRABLE may be set deferred. Deferral still checks the rule by transaction end; it is not equivalent to disabling integrity across a maintenance window.

Use a controlled disable-and-reload workflow

The legacy lesson suggested disabling the child foreign key, deleting and reloading only the parent table, and then enabling the constraint. That sequence is safe only if every child key matches the restored parent data. Otherwise, the reload leaves orphaned rows and full validation fails. A controlled workflow prevents unrelated writes, reloads parent and child data coherently, and checks the relationship before re-enabling it.

SELECT d.order_id
FROM   order_details d
WHERE  NOT EXISTS (
           SELECT 1
           FROM   orders o
           WHERE  o.order_id = d.order_id
       );

A zero-row result supports revalidation, but it does not replace the database check. Concurrent DML or a different rule can still change the result. The preferred final state for ordinary application integrity is explicit full validation:

ALTER TABLE order_details
ENABLE VALIDATE CONSTRAINT order_parent;

Oracle checks the stored rows and enforces the foreign key for later DML. If a violation exists, the statement returns an error and the constraint remains disabled. The referenced primary or unique constraint must also be enabled before its foreign key can be enabled.

A staged deployment can first protect new DML without validating earlier rows:

ALTER TABLE order_details
ENABLE NOVALIDATE CONSTRAINT order_parent;

That state is useful only when its limitation is explicit: subsequent changes are checked, but preexisting rows have not been proven compliant. Oracle can transition one constraint from ENABLE NOVALIDATE to ENABLE VALIDATE without blocking reads, writes, or other DDL under the documented behavior, but that does not make every constraint operation or surrounding deployment interruption-free.

Verify the final constraint state

Do not infer integrity from a successful deployment script or from the word ENABLED alone. Query both the enforcement and validation columns in USER_CONSTRAINTS:

SELECT table_name,
       constraint_name,
       constraint_type,
       status,
       validated,
       deferrable,
       deferred,
       last_change
FROM   user_constraints
WHERE  constraint_name = 'ORDER_PARENT';

The desired ordinary result is STATUS = 'ENABLED' and VALIDATED = 'VALIDATED'. An enabled but not validated constraint protects subsequent DML without proving historical rows. A disabled and not validated constraint provides neither enforcement nor a guarantee. Record the intended state as part of the migration acceptance criteria so a temporary relaxation does not silently become permanent.

Apply a production safety checklist

1. Confirm the correct schema or PDB and capture the current table and dependent-object definitions.
2. Inventory foreign keys, SQL assertions, indexes, triggers, grants, synonyms, views, stored programs, and external application dependencies.
3. Choose deliberately among a recoverable drop, a purged drop, permanent constraint removal, or a temporary constraint-state transition.
4. Control application writes when enforcement will be disabled, and preserve a tested recovery path for destructive DDL.
5. Repair orphaned or otherwise nonconforming data before requesting full validation.
6. Finish ordinary integrity changes with ENABLE VALIDATE and verify both STATUS and VALIDATED.
7. Verify the recycle bin, object validity, grants, synonyms, constraints, row relationships, and application behavior after the change.

The essential distinction is between removing an object, removing a rule, and temporarily changing enforcement. A short statement can alter many downstream assumptions, so production work must pair the DDL with dependency discovery, data validation, recovery planning, and post-change verification. The next lesson concludes the module on creating and modifying Oracle table structures.