Oracle SQL ALTER TABLE: Adding, Modifying and Dropping Columns and Constraints

Oracle SQL ALTER TABLE and Managing Schema Objects: Complete Guide

The ALTER TABLE statement is the primary way to modify the structure of an existing table. Whether you need to add columns, change data types, add or remove constraints, or manage schema objects, understanding ALTER TABLE is essential for any Oracle developer or DBA.

Learning Objectives
By the end of this guide you will master adding, modifying, and dropping columns and constraints, understand the restrictions and best practices for each operation, learn safer alternatives like SET UNUSED, and handle table management tasks including DROP TABLE, RENAME, and the Recycle Bin.

1. ADD Column — Adding New Columns

You can add one or more columns to an existing table. New columns are always appended at the end of the table.

-- Add a nullable column
ALTER TABLE employees
ADD (bonus_pct NUMBER(5,2));

-- Add a column with DEFAULT and NOT NULL
ALTER TABLE employees
ADD (status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL);

-- Add multiple columns at once
ALTER TABLE departments
ADD (budget NUMBER(10,2), region VARCHAR2(20));

Important: Adding a NOT NULL column without a DEFAULT value fails if the table already contains rows. Existing rows would receive NULL, violating the constraint. Always provide a DEFAULT when adding NOT NULL to populated tables.

2. MODIFY Column — Changing Column Definitions

Use MODIFY to change a column’s data type, size, default value, or nullability.

-- Increase column size (always allowed)
ALTER TABLE employees
MODIFY (last_name VARCHAR2(50));

-- Add NOT NULL (only if no NULL values exist)
ALTER TABLE employees
MODIFY (job_id VARCHAR2(10) NOT NULL);

-- Change DEFAULT value
ALTER TABLE departments
MODIFY (location_id NUMBER(4) DEFAULT 1700);

-- Make column nullable
ALTER TABLE employees
MODIFY (commission_pct NULL);

3. Restrictions on MODIFY Operations

Operation	Allowed?	Condition / Restriction
Increase column size	Always	No restrictions
Decrease column size	Only if all existing values fit	Fails if any value is too large
Change data type	Only if column is empty or all NULLs	Fails if data exists
Add NOT NULL	Only if no NULLs exist	Fails if any row has NULL
Remove NOT NULL	Always	No restrictions
Change DEFAULT	Always	Affects only future inserts

4. DROP COLUMN — Removing Columns

DROP COLUMN permanently deletes the column and all its data.

-- Drop a single column
ALTER TABLE employees
DROP COLUMN bonus_pct;

-- Drop multiple columns
ALTER TABLE employees
DROP (bonus_pct, region_code);

Cannot drop a column used in constraints: You must first drop any PRIMARY KEY, UNIQUE, or FOREIGN KEY constraints that reference the column.

5. SET UNUSED — A Safer Alternative to DROP COLUMN

SET UNUSED marks a column as unavailable without physically removing the data immediately. It is much faster on large tables.

-- Mark column(s) as unused
ALTER TABLE employees
SET UNUSED COLUMN bonus_pct;

-- Or multiple columns
ALTER TABLE employees
SET UNUSED (bonus_pct, region_code);

-- Physically remove unused columns later
ALTER TABLE employees
DROP UNUSED COLUMNS;

	DROP COLUMN	SET UNUSED
Physical removal	Immediate	Delayed (until DROP UNUSED COLUMNS)
Performance on large tables	Slower	Faster (metadata only)
Reversible?	No	No

6. ADD CONSTRAINT — Adding Constraints After Table Creation

-- Add PRIMARY KEY
ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PRIMARY KEY (department_id);

-- Add FOREIGN KEY
ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
    REFERENCES departments(department_id);

-- Add UNIQUE or CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT emp_email_uk UNIQUE (email);

Existing data is validated: When adding a constraint, Oracle checks all current rows. The operation fails if any row violates the new constraint.

7. DROP CONSTRAINT and DISABLE / ENABLE

-- Drop a constraint
ALTER TABLE employees
DROP CONSTRAINT emp_dept_fk;

-- Drop PRIMARY KEY with CASCADE
ALTER TABLE departments
DROP PRIMARY KEY CASCADE;

-- Disable / Enable a constraint
ALTER TABLE employees
DISABLE CONSTRAINT emp_dept_fk;

ALTER TABLE employees
ENABLE CONSTRAINT emp_dept_fk;

8. DROP TABLE, PURGE and the Recycle Bin

-- Drop table (moves to Recycle Bin)
DROP TABLE emp_history;

-- Restore from Recycle Bin
FLASHBACK TABLE emp_history TO BEFORE DROP;

-- Permanently drop (bypass Recycle Bin)
DROP TABLE emp_history PURGE;

-- Empty Recycle Bin
PURGE RECYCLEBIN;

9. RENAME and COMMENT

-- Rename table
RENAME employees TO emp;

-- Rename column
ALTER TABLE employees
RENAME COLUMN last_name TO surname;

-- Add comments
COMMENT ON TABLE employees IS 'Stores all employee records';
COMMENT ON COLUMN employees.salary IS 'Monthly salary in USD';

10. Interactive Practice Quiz

Q1. A table already has 500 rows. What happens when you run this?
ALTER TABLE orders ADD (delivery_date DATE NOT NULL);

A. Column is added and all rows get NULL B. Statement fails — NOT NULL requires a DEFAULT when rows exist C. Column is added with SYSDATE as default D. Statement succeeds only if table is empty

Q2. You want to drop the EMPLOYEE_ID column (PRIMARY KEY) from EMPLOYEES, but another table has a FOREIGN KEY referencing it. What must you do first?

A. Nothing — DROP COLUMN handles it automatically B. Drop the FOREIGN KEY constraint first C. Use SET UNUSED instead D. Disable the PRIMARY KEY first

Q3. You accidentally ran DROP TABLE emp_archive. How can you recover it immediately?

A. ROLLBACK; B. FLASHBACK TABLE emp_archive TO BEFORE DROP; C. Recovery is impossible D. UNDROP TABLE emp_archive;

Conclusion

Mastering ALTER TABLE and schema management operations gives you full control over your database structure. Understanding the restrictions, performance implications, and safer alternatives (like SET UNUSED) helps you make confident, production-safe changes.

Practice these commands regularly in a test environment, and always consider the impact on existing data and constraints before making structural changes.