Lesson 21 Advanced Level
21 of 30 lessons

ALTER TABLE

Learn how to modify table structure by adding, dropping, or modifying columns and constraints. ALTER TABLE is essential for database maintenance.

Introduction

The ALTER TABLE statement allows you to modify the structure of an existing table. You can add, modify, or drop columns and constraints.

Common ALTER TABLE Actions

  • Add new columns
  • Modify existing columns
  • Drop columns
  • Add or drop constraints

Add a Column

ALTER TABLE employees
ADD (email VARCHAR2(100));

Modify a Column

ALTER TABLE employees
MODIFY (salary NUMBER(10,2));

Drop a Column

ALTER TABLE employees
DROP COLUMN email;

Add a Constraint

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

Drop a Constraint

ALTER TABLE employees
DROP CONSTRAINT emp_dept_fk;

Practice Task

  • Add a new column phone_number to the employees table.
  • Modify the first_name column to accept up to 100 characters.
  • Drop the phone_number column again.
  • Add a foreign key constraint linking employees.department_id to departments.dept_id.
Previous Lesson Next Lesson