Lesson 13 — Modifying Structure

ALTER TABLE

ALTER TABLE modifies an existing table structure. In production systems these operations can be disruptive on large tables — always plan them carefully.

SQL — Column operations 
-- ADD COLUMN:
ALTER TABLE learners
    ADD COLUMN province VARCHAR(50) AFTER city;

ALTER TABLE learners
    ADD COLUMN deleted_at TIMESTAMP NULL,
    ADD COLUMN deleted_by INT NULL;  -- multiple columns in one statement

-- DROP COLUMN:
ALTER TABLE learners
    DROP COLUMN province;

-- MODIFY / CHANGE COLUMN (MySQL):
ALTER TABLE learners
    MODIFY COLUMN full_name VARCHAR(200) NOT NULL;  -- change type/constraint

-- RENAME COLUMN (MySQL 8+, PostgreSQL):
ALTER TABLE learners
    RENAME COLUMN full_name TO learner_name;

-- RENAME TABLE:
ALTER TABLE learners RENAME TO programme_learners;
SQL — Constraint operations 
-- ADD CONSTRAINT:
ALTER TABLE learners
    ADD CONSTRAINT chk_mark
    CHECK (mark BETWEEN 0 AND 100);

ALTER TABLE learners
    ADD CONSTRAINT uq_email
    UNIQUE (email);

-- ADD FOREIGN KEY:
ALTER TABLE assessments
    ADD CONSTRAINT fk_assess_learner
    FOREIGN KEY (learner_id) REFERENCES learners(learner_id)
    ON DELETE CASCADE;

-- DROP CONSTRAINT:
ALTER TABLE learners
    DROP CONSTRAINT chk_mark;     -- standard SQL
    -- MySQL: DROP CHECK chk_mark
    -- MySQL FK: DROP FOREIGN KEY fk_name

-- ADD INDEX:
ALTER TABLE learners
    ADD INDEX idx_city (city);

-- DROP INDEX:
ALTER TABLE learners
    DROP INDEX idx_city;
SQL — Changing defaults and nullability 
-- Changing a column's default:
ALTER TABLE learners
    ALTER COLUMN is_active SET DEFAULT FALSE;

-- Remove a default:
ALTER TABLE learners
    ALTER COLUMN is_active DROP DEFAULT;

-- SET NOT NULL (column must have no NULLs first):
-- 1. Fill any NULLs:
UPDATE learners SET city = 'Unknown' WHERE city IS NULL;
-- 2. Then add the constraint:
ALTER TABLE learners
    MODIFY COLUMN city VARCHAR(50) NOT NULL;

Practice Task

Your Turn

Given a learners table: (1) add a linkedin_url VARCHAR(200) column; (2) add a UNIQUE constraint on id_number; (3) change the course column to allow 100 characters instead of 50; (4) add an index on city; (5) add a created_by INT column with a FK to a users table. Write the SQL for each, in the correct order where dependencies exist.

Common Mistakes

  • Adding NOT NULL to a column that already has NULLs — the ALTER fails. Fill NULLs first.
  • Dropping a column referenced by a FK in another table — constraint violation. Drop the FK first.
  • ALTER TABLE on large tables locks the table for the duration — schedule these during maintenance windows.
  • Syntax differences between MySQL (MODIFY COLUMN) and PostgreSQL (ALTER COLUMN).

Professional Tip

In production, use a database migration tool (Flyway, Liquibase, Alembic) to track schema changes. Never run ALTER TABLE manually on a production database without a backup and a rollback plan.

Mini Quiz

What must you do before adding NOT NULL to a column that may contain NULLs?