Study guide
11.3 Modifying Tables & Managing Constraints
Oracle Database SQL Certified Associate 1Z0-071
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Relational Database Concepts & Logical Modeling
1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals
2 Chapter 2: Retrieving Data with SQL SELECT
2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection
3 Chapter 3: Restricting and Sorting Query Data
3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives
4 Chapter 4: Customizing Output with Single-Row Functions
4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions
5 Chapter 5: Conversion Functions and Conditional Expressions
5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE
6 Chapter 6: Reporting Aggregated Data with Group Functions
6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause
7 Chapter 7: Displaying Data from Multiple Tables (Joins)
7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL
8 Chapter 8: Solving Queries with Subqueries
8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps
9 Chapter 9: Set Operators
9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules
10 Chapter 10: Managing Tables with DML & Transaction Control
10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT
11 Chapter 11: Managing Tables and Relationships with DDL
11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables
12 Chapter 12: Views
12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints
13 Chapter 13: Indexes, Synonyms, and Sequences
13.1 Sequences 13.2 Indexes 13.3 Synonyms
14 Chapter 14: Controlling User Access & Privileges
14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management
15 Chapter 15: Oracle Data Dictionary Views
15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments
16 Chapter 16: Managing Data in Different Time Zones
16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment
11.3 Modifying Tables & Managing Constraints
Key Takeaways Explain This Topic AI
ALTER TABLE allows adding columns (appended to table end), modifying columns, dropping columns, renaming objects, and managing constraint states.
Widening column size is always permitted; narrowing column size or changing datatype family requires the column to contain all NULLs or the table to be empty.
SET UNUSED COLUMN provides a fast metadata-only operation to immediately hide obsolete columns without locking large tables, deferring physical space reclamation to DROP UNUSED COLUMNS.
Constraints support four state combinations: ENABLE/DISABLE and VALIDATE/NOVALIDATE; ENABLE NOVALIDATE enforces rules on future DML while ignoring existing dirty data.
DROP TABLE moves tables to the Recyclebin (BIN$ naming) for recovery via FLASHBACK TABLE TO BEFORE DROP, unless PURGE is explicitly specified.
Last updated: August 2026
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!)
Modification Attempt Condition Required for Success Error if Condition Not Met
Increase Column Width (e.g., VARCHAR2(20) → \rightarrow → VARCHAR2(50) ) Always permitted. Allowed whether table is empty, full, or contains NULLs. None.
Decrease Column Width (e.g., VARCHAR2(50) → \rightarrow → VARCHAR2(20) ) Permitted only if all existing values in the column fit within the new length, OR all rows are NULL , OR the table is empty. ORA-01441: cannot decrease column length because some value is too big
Change Datatype Family (e.g., VARCHAR2 → \rightarrow → NUMBER or DATE ) Permitted only if the column contains all NULL values for every row, OR the table is empty. ORA-01439: column to be modified must be empty to change datatype
Increase Numeric Precision (e.g., NUMBER(4) → \rightarrow → NUMBER(8) ) Always permitted. None.
Decrease Numeric Precision (e.g., NUMBER(8) → \rightarrow → NUMBER(4) ) Permitted only if all existing values fit or all rows are NULL. ORA-01440: column to be modified must be empty to decrease precision or scale
Add NOT NULL Constraint Permitted only if the column contains zero NULL values across all existing rows. ORA-02296: cannot enable (NOT NULL) - null values found
Modify DEFAULT Value Always permitted. Affects only future INSERT operations ; does not alter existing row data. None.
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 n 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:
Enforcement Status: ENABLE (enforces constraint on new DML) vs. DISABLE (ignores constraint on new DML).
Validation Status: VALIDATE (guarantees existing data conforms) vs. NOVALIDATE (does not check existing data). Constraint States = ( ENABLE ∣ DISABLE ) × ( VALIDATE ∣ NOVALIDATE ) \text{Constraint States} = (\text{ENABLE} \mid \text{DISABLE}) \times (\text{VALIDATE} \mid \text{NOVALIDATE}) Constraint States = ( ENABLE ∣ DISABLE ) × ( VALIDATE ∣ 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
Constraint State Behavior on New DML Checks Existing Data? Primary Use Case
ENABLE VALIDATE (Default ENABLE ) Enforced. Rejects violating INSERT / UPDATE . Yes. Fails if any existing row violates. Standard production state.
ENABLE NOVALIDATE Enforced. Rejects violating INSERT / UPDATE . No. Existing dirty rows are ignored. Fast constraint activation after legacy data migration without cleaning historic records.
DISABLE NOVALIDATE (Default DISABLE ) Not enforced. Violating DML is permitted. No. Bulk data loading performance (drops associated unique index).
DISABLE VALIDATE Prevents any DML that modifies constrained columns. Yes. Ensures existing data conforms. Read-only partition management or maintenance without unique index overhead.
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 : Recyclebin Name Format: BIN $ unique_id $ version \text{Recyclebin Name Format: } \text{BIN}\$\text{unique\_id}\$\text{version} Recyclebin Name Format: BIN $ unique_id $ 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.