11.3 Modifying Tables & Managing Constraints

11.3 Modifying Tables & Managing Constraints

After tables and constraints are created, database schemas evolve as business requirements change. The ALTER TABLE statement provides a comprehensive set of DDL operations to add, modify, drop, rename, and manage columns and constraints without dropping and recreating tables.

For the 1Z0-071 exam, you must master column modification restrictions, the performance distinction between DROP COLUMN and SET UNUSED, the four constraint enforcement states (ENABLE/DISABLE $\times$ VALIDATE/NOVALIDATE), and the mechanics of Flashback Drop and the Recyclebin.

---

ALTER TABLE: Column Operations

                           ALTER TABLE OPERATIONS
                                     |
     +---------------+---------------+---------------+---------------+
     |               |               |               |               |
    ADD           MODIFY           DROP          UNUSED          RENAME
  - New cols    - Widen size    - DROP COLUMN  - Fast hide     - RENAME COL
  - Constraints - Datatypes     - PURGE data   - Deferred drop - RENAME TO
                - Defaults/NN   - Locks table

1. Adding Columns (ADD)

ALTER TABLE employees ADD (
    mobile_no    VARCHAR2(15),
    hire_bonus   NUMBER(8, 2) DEFAULT 0 NOT NULL
);

• New columns are appended to the end of the table by default.
• If a new column is declared as NOT NULL, it must include a DEFAULT value if the table already contains rows, or the statement will fail with ORA-01758: table must be empty to add mandatory (NOT NULL) column.

2. Modifying Existing Columns (MODIFY)

ALTER TABLE employees MODIFY (
    mobile_no    VARCHAR2(25),
    salary       NUMBER(10, 2) DEFAULT 1000
);

Column Modification Rules Matrix (Exam Essentials!)

---

Dropping Columns vs. Setting Columns UNUSED

When removing columns from large production tables, database administrators must choose between immediate physical deletion and deferred logical removal.

+-------------------------------------------------------------------------+
|                   DROP COLUMN VS. SET UNUSED COLUMN                     |
+-------------------------------------------------------------------------+
| Feature              | DROP COLUMN                  | SET UNUSED COLUMN |
| :------------------- | :--------------------------- | :---------------- |
| **Operation Type**   | Physical removal + space recovery | Logical marking (Metadata only) |
| **Execution Speed**  | Slow on large tables (High I/O)| Instantaneous (Microseconds) |
| **Table Locking**    | Exclusive lock for duration | Brief metadata lock |
| **Query Visibility** | Column no longer exists      | Column hidden from queries |
| **Can Be Undone?**   | NO                           | NO (Cannot "SET USED") |
| **Space Reclaimed?** | Immediately                  | Only when DROP UNUSED runs |
+-------------------------------------------------------------------------+

1. DROP COLUMN Mechanics

-- Drop a single column:
ALTER TABLE employees DROP COLUMN ssn CASCADE CONSTRAINTS;

-- Drop multiple columns:
ALTER TABLE employees DROP (fax_number, pager_number);

• CASCADE CONSTRAINTS: Automatically drops any multicolumn constraints or foreign keys in other tables referencing this column.
• Restriction: You cannot drop all columns from a table; at least one column must remain.

2. SET UNUSED & DROP UNUSED COLUMNS

-- Step 1: Instantly mark columns unused during peak hours
ALTER TABLE orders SET UNUSED (shipping_notes, tracking_code) CASCADE CONSTRAINTS;

-- Step 2: Physically reclaim space during off-peak maintenance window
ALTER TABLE orders DROP UNUSED COLUMNS CHECKPOINT 1000;

• CHECKPOINT n: Flushes redo logs and minimizes undo segment usage every $n$ processed rows during the drop operation, preventing transaction log exhaustion on multi-gigabyte tables.

---

Managing Constraint States

Oracle constraints possess two independent state attributes:

1. Enforcement Status: ENABLE (enforces constraint on new DML) vs. DISABLE (ignores constraint on new DML).
2. Validation Status: VALIDATE (guarantees existing data conforms) vs. NOVALIDATE (does not check existing data).

$\text{Constraint States} = (\text{ENABLE} \mid \text{DISABLE}) \times (\text{VALIDATE} \mid \text{NOVALIDATE})$

                       CONSTRAINT STATE QUADRANT

                      NOVALIDATE              VALIDATE
               +-----------------------+-----------------------+
               |   DISABLE NOVALIDATE  |   DISABLE VALIDATE    |
               | - Constraint OFF      | - Constraint OFF      |
   DISABLE     | - Existing data unchecked | - Table is READ-ONLY  |
               | - Default DISABLE     |   for constrained col |
               +-----------------------+-----------------------+
               |   ENABLE NOVALIDATE   |    ENABLE VALIDATE    |
               | - Constraint ON (new) | - Constraint ON (new) |
    ENABLE     | - Existing dirty data | - All data conforms   |
               |   ignored             | - Default ENABLE      |
               +-----------------------+-----------------------+

Detailed Constraint State Behaviors

Managing Constraints via ALTER TABLE

-- Add a foreign key constraint in disabled state:
ALTER TABLE employees ADD CONSTRAINT emp_dept_fk
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
    DISABLE;

-- Enable constraint without validating existing rows:
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT emp_dept_fk;

-- Fully enable and validate constraint:
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT emp_dept_fk;

-- Drop a constraint:
ALTER TABLE departments DROP CONSTRAINT dept_pk CASCADE;

---

Dropping Tables, Flashback Drop & The Recyclebin

When a table is no longer needed, it can be dropped using DROP TABLE:

DROP TABLE project_archives CASCADE CONSTRAINTS PURGE;

• CASCADE CONSTRAINTS: Automatically drops referential integrity constraints in other child tables that point to this table's primary or unique keys.
• PURGE: Permanently removes the table and releases its storage immediately, bypassing the Recyclebin. The table cannot afterwards be recovered with Flashback Drop.

Flashback Drop & Recyclebin Mechanics

If DROP TABLE is executed without the PURGE clause, Oracle does not physically delete the data immediately. Instead, it renames the table and its dependent objects to a unique system name in the Recyclebin:

$\text{Recyclebin Name Format: } \text{BIN}\$\text{unique\_id}\$\text{version}$

-- 1. View objects in the Recyclebin
SELECT object_name, original_name, droptime FROM user_recyclebin;

-- 2. Restore dropped table
FLASHBACK TABLE employees TO BEFORE DROP;

-- 3. Restore and rename in one step
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_restored;

-- 4. Empty the recyclebin
PURGE RECYCLEBIN;             -- Purges current user's dropped objects
PURGE TABLE employees;         -- Purges specific table from recyclebin

What Flashback Drop Restores (and What It Does NOT)

• Restores: Table rows, columns, unique/check constraints, triggers, and indexes (indexes retain their BIN$... names unless renamed).
• Does NOT Restore: Foreign key constraints referencing other tables are lost and must be manually recreated.