1Z0-071 Exam Prep
Chapter 15
Volume 4
ALTER TABLE is one of the most rule-heavy DDL topics on the 1Z0-071 exam. The questions are not just about syntax — they test the restrictions. You cannot always change a column's data type, you cannot always add a NOT NULL constraint, and you cannot always drop a column. Knowing exactly when each operation is blocked is what the exam targets.
This post covers every ALTER TABLE operation tested on the exam and the specific conditions that allow or prevent each one.
In this post
- ADD column — adding new columns
- MODIFY column — changing column definitions
- Restrictions on MODIFY
- DROP COLUMN — removing columns
- SET UNUSED — a safer alternative to DROP COLUMN
- ADD CONSTRAINT — adding constraints after table creation
- DROP CONSTRAINT — removing constraints
- DISABLE and ENABLE CONSTRAINT
- DROP TABLE, PURGE and the Recycle Bin
- RENAME and COMMENT
- Quick reference
- Practice questions
01 — ADD column — adding new columns
Adding a new column always works — there are no blocking conditions. The new column is added as the last column in the table. You cannot specify where in the column order the new column appears.
-- Add a nullable column ALTER TABLE employees ADD (bonus_pct NUMBER(5,2)); -- New column added at the end, all existing rows have NULL in this column -- Add a column with a DEFAULT value ALTER TABLE employees ADD (status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL); -- Adding NOT NULL with a DEFAULT is allowed even when rows exist -- Existing rows get the DEFAULT value, satisfying the NOT NULL constraint -- Add multiple columns at once ALTER TABLE departments ADD (budget NUMBER(10,2), region VARCHAR2(20));
Adding NOT NULL without a DEFAULT — blocked if rows exist:
ALTER TABLE employees ADD (col VARCHAR2(10) NOT NULL) fails when the table already contains rows. Existing rows would get NULL in the new column, violating NOT NULL immediately. The fix is to add a DEFAULT value alongside NOT NULL.
02 — MODIFY column — changing column definitions
MODIFY changes the data type, size, default value, or constraint status of an existing column.
-- Increase column size ALTER TABLE employees MODIFY (last_name VARCHAR2(50)); -- VARCHAR2(25) → VARCHAR2(50): always allowed -- Add NOT NULL to an existing column ALTER TABLE employees MODIFY (job_id VARCHAR2(10) NOT NULL); -- Only allowed if no existing row has NULL in job_id -- Change the DEFAULT value ALTER TABLE departments MODIFY (location_id NUMBER(4) DEFAULT 1700); -- Changes the default for future inserts only -- Existing rows are not affected by a DEFAULT change -- Remove NOT NULL (make column nullable) ALTER TABLE employees MODIFY (commission_pct NULL); -- Removes the NOT NULL constraint from the column — always allowed
03 — Restrictions on MODIFY
Not every MODIFY operation is allowed. The restrictions depend on what data the column currently holds.
| MODIFY operation | Allowed? | Condition |
|---|---|---|
| Increase column size (VARCHAR2, CHAR, NUMBER precision) | Always | No restrictions |
| Decrease column size | Only if column is empty or all values fit the new size | Fails if any existing value is too wide |
| Change data type | Only if the column is empty (no rows, or all NULLs) | Fails if column contains data |
| Add NOT NULL | Only if no existing row has NULL in that column | Fails if any NULL exists |
| Remove NOT NULL | Always | No restrictions |
| Change DEFAULT | Always | Only affects future inserts — existing rows unchanged |
-- WRONG: shrinking a column that has data wider than new size ALTER TABLE employees MODIFY (last_name VARCHAR2(5)); -- Error if any last_name value exceeds 5 characters -- WRONG: changing data type when column has data ALTER TABLE employees MODIFY (employee_id VARCHAR2(10)); -- Error: cannot change NUMBER to VARCHAR2 when rows exist -- WRONG: adding NOT NULL when NULLs exist in that column ALTER TABLE employees MODIFY (commission_pct NOT NULL); -- Error: some employees have NULL commission_pct
04 — DROP COLUMN — removing columns
DROP COLUMN permanently removes a column and all its data. The operation cannot be rolled back. There are a few conditions that prevent it.
-- Drop a single column ALTER TABLE employees DROP COLUMN bonus_pct; -- Column and all its data are permanently removed -- Cannot be rolled back with ROLLBACK -- Drop multiple columns at once ALTER TABLE employees DROP (bonus_pct, region_code); -- Cannot drop the only remaining column ALTER TABLE one_col_table DROP COLUMN the_only_column; -- Error: cannot drop all columns from a table
Cannot drop a column that is part of a PRIMARY KEY or multi-column constraint: If a column participates in a PRIMARY KEY, UNIQUE constraint, or composite FOREIGN KEY, you must drop the constraint first before dropping the column. The exam tests this — presenting a DROP COLUMN that fails because of an existing constraint.
05 — SET UNUSED — a safer alternative to DROP COLUMN
SET UNUSED marks a column as unavailable without immediately removing it from the physical storage. It is faster than DROP COLUMN on large tables and can be done during business hours without the overhead of a full physical remove.
-- Mark column as unused — fast, no physical removal yet ALTER TABLE employees SET UNUSED COLUMN bonus_pct; -- Column is immediately invisible — cannot be queried or referenced -- Data still occupies storage until DROP UNUSED COLUMNS is run -- Mark multiple columns unused at once ALTER TABLE employees SET UNUSED (bonus_pct, region_code); -- Physically remove all unused columns (run during off-peak hours) ALTER TABLE employees DROP UNUSED COLUMNS;
| DROP COLUMN | SET UNUSED | |
|---|---|---|
| Immediate physical removal? | Yes | No — data remains until DROP UNUSED COLUMNS |
| Column visible after operation? | No | No — immediately invisible |
| Can be reversed? | No | No — SET UNUSED cannot be undone |
| Performance on large tables | Slower — rewrites data blocks | Faster — metadata change only |
06 — ADD CONSTRAINT — adding constraints after table creation
Constraints can be added to an existing table using ALTER TABLE ADD CONSTRAINT. The same rules that apply to constraint creation during CREATE TABLE apply here — plus one additional check: any existing data must satisfy the new constraint.
-- Add a PRIMARY KEY to an existing table ALTER TABLE departments ADD CONSTRAINT dept_id_pk PRIMARY KEY (department_id); -- Add a FOREIGN KEY constraint ALTER TABLE employees ADD CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id); -- Add a UNIQUE constraint ALTER TABLE employees ADD CONSTRAINT emp_email_uk UNIQUE (email); -- Add a CHECK constraint ALTER TABLE employees ADD CONSTRAINT emp_sal_ck CHECK (salary > 0); -- NOT NULL is added via MODIFY, not ADD CONSTRAINT ALTER TABLE employees MODIFY (last_name NOT NULL); -- correct way to add NOT NULL
Existing data is validated: Adding a constraint to a table with existing data fails if any row violates the constraint. For example, adding a UNIQUE constraint fails if duplicates already exist. Adding a FOREIGN KEY fails if any child column value has no matching parent row.
07 — DROP CONSTRAINT — removing constraints
Constraints are dropped by name. This is why naming constraints at creation time matters — unnamed constraints get a system-generated name that is harder to reference later.
-- Drop a named constraint ALTER TABLE employees DROP CONSTRAINT emp_dept_fk; -- Drop PRIMARY KEY constraint ALTER TABLE departments DROP PRIMARY KEY; -- Also drops the unique index that was created to support it -- Drop PRIMARY KEY and CASCADE to dependent foreign keys ALTER TABLE departments DROP PRIMARY KEY CASCADE; -- Drops the PK and also drops any FK constraints in child tables -- that referenced this primary key -- Drop a UNIQUE constraint by column ALTER TABLE employees DROP UNIQUE (email);
CASCADE drops dependent constraints: If you drop a PRIMARY KEY that child tables reference via FOREIGN KEY, you must use CASCADE or Oracle raises an error. CASCADE automatically removes the dependent FK constraints in the child tables. Without CASCADE, the drop is rejected if any FK references the PK.
08 — DISABLE and ENABLE CONSTRAINT
Constraints can be disabled temporarily without dropping them. This is useful for bulk data loading when you want to skip constraint checking for performance, then re-enable the constraint afterward.
-- Disable a constraint ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk; -- Constraint definition is kept but not enforced -- DML that would violate it is now allowed -- Re-enable a constraint ALTER TABLE employees ENABLE CONSTRAINT emp_dept_fk; -- Oracle validates ALL existing rows against the constraint -- If any row violates it, the ENABLE fails -- Disable PRIMARY KEY ALTER TABLE departments DISABLE PRIMARY KEY CASCADE; -- CASCADE also disables dependent FK constraints in child tables
ENABLE validates existing data: When you re-enable a constraint, Oracle checks every existing row. If data was loaded while the constraint was disabled and that data violates the constraint, the ENABLE statement fails. You must fix the bad data first.
09 — DROP TABLE, PURGE and the Recycle Bin
DROP TABLE moves a table to the Recycle Bin by default. The table is not immediately gone — it can be restored with FLASHBACK TABLE. PURGE removes it permanently.
-- Drop a table (moves to Recycle Bin) DROP TABLE emp_history; -- Table appears to be gone but is in the Recycle Bin -- Indexes and constraints on this table are also dropped -- Restore from Recycle Bin FLASHBACK TABLE emp_history TO BEFORE DROP; -- Drop permanently — bypass Recycle Bin DROP TABLE emp_history PURGE; -- Cannot be recovered — gone immediately -- Empty the Recycle Bin PURGE RECYCLEBIN;
| Action | Recoverable? | How |
|---|---|---|
DROP TABLE t | Yes — until purged | FLASHBACK TABLE t TO BEFORE DROP |
DROP TABLE t PURGE | No | Permanently removed |
TRUNCATE TABLE t | No | DDL — no Recycle Bin, no ROLLBACK |
10 — RENAME and COMMENT
Oracle allows renaming tables and columns, and adding descriptive comments to tables and columns. Both are tested on the exam at a basic level.
-- Rename a table RENAME employees TO emp; -- All constraints, indexes, and grants on the table are preserved -- Views and synonyms that reference the old name become invalid -- Rename a column (ALTER TABLE syntax) ALTER TABLE employees RENAME COLUMN last_name TO surname; -- Add a comment to a table COMMENT ON TABLE employees IS 'Stores all employee records'; -- Add a comment to a column COMMENT ON COLUMN employees.salary IS 'Monthly salary in USD'; -- View comments from the data dictionary SELECT * FROM user_tab_comments WHERE table_name = 'EMPLOYEES'; SELECT * FROM user_col_comments WHERE table_name = 'EMPLOYEES';
RENAME invalidates dependent views and synonyms: Renaming a table does not update views or synonyms that reference it — they become invalid. Constraints, indexes, and grants move with the table automatically. The exam tests which objects are preserved and which become invalid after a rename.
11 — Quick reference
| Operation | Allowed? | Restriction |
|---|---|---|
| ADD column (nullable) | Always | Added as last column — position cannot be specified |
| ADD column NOT NULL without DEFAULT | Only if table is empty | Fails when rows exist — no default value for existing rows |
| MODIFY — increase size | Always | None |
| MODIFY — decrease size | Only if all values fit | Fails if any value is too wide for the new size |
| MODIFY — change data type | Only if column is empty | Fails if column contains data |
| MODIFY — add NOT NULL | Only if no NULLs exist | Fails if any row has NULL in that column |
| DROP COLUMN | Usually | Cannot drop if only column; cannot drop PK/constraint column directly |
| SET UNUSED | Always | Irreversible — cannot be made visible again |
| ADD CONSTRAINT | Only if existing data satisfies it | Fails if any row violates the new constraint |
| DROP PRIMARY KEY | Usually — use CASCADE if FK references exist | Also drops the supporting unique index |
| ENABLE CONSTRAINT | Only if all rows satisfy it | Fails if bad data was inserted while disabled |
| DROP TABLE | Always | Goes to Recycle Bin — use PURGE to bypass |
| RENAME table | Always | Views and synonyms referencing old name become invalid |
12 — Practice questions
Q1 — A table has 500 rows. A developer runs:
What happens?
ALTER TABLE orders ADD (delivery_date DATE NOT NULL);What happens?
Q2 — A developer needs to drop the EMPLOYEE_ID column from the EMPLOYEES table. EMPLOYEE_ID is the PRIMARY KEY and the DEPT_HISTORY table has a FOREIGN KEY referencing it. What must the developer do first?
Q3 — A table is dropped with
DROP TABLE emp_archive;. A developer immediately realises this was a mistake. What is the correct recovery approach?Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
Chapter 15 covers the full schema object management lifecycle — ALTER, DROP, RENAME, FLASHBACK — with practice sets for every restriction and edge case tested on the exam. Instant PDF download.
Get the full guide — $25 →
Chapter 15 covers the full schema object management lifecycle — ALTER, DROP, RENAME, FLASHBACK — with practice sets for every restriction and edge case tested on the exam. Instant PDF download.
No comments:
Post a Comment