Skip to content
Tutorials Center
My learning

Topics

Lesson 74 of 125 ยท Designing Databases

ALTER TABLE and DROP TABLE

Change tables that already hold data. Add, rename and drop columns with ALTER TABLE, rebuild tables safely in SQLite, drop tables and manage schema migrations.

No schema survives contact with real users. Six months after launch, the product team wants a phone number on customer accounts, a column name turns out to be misleading, and an old table nobody uses is cluttering the database. You can't just drop everything and run a new CREATE TABLE: the tables are full of data that must survive the change.

ALTER TABLE changes the structure of an existing table while keeping its rows, and DROP TABLE removes a table entirely. This lesson covers both, explains what SQLite's ALTER TABLE can and can't do (and the standard workaround when it can't), and introduces migrations, the disciplined way teams change schemas over time.

Everything here builds on CREATE TABLE and Constraints.

What ALTER TABLE can do

The SQL standard and the major databases support a common set of changes. SQLite supports a smaller set:

Change SQLite PostgreSQL MySQL / MariaDB SQL Server
Rename table RENAME TO RENAME TO RENAME TO sp_rename
Add column ADD COLUMN ADD COLUMN ADD COLUMN ADD
Rename column RENAME COLUMN (3.25+) RENAME COLUMN RENAME COLUMN (8.0+) sp_rename
Drop column DROP COLUMN (3.35+) DROP COLUMN DROP COLUMN DROP COLUMN
Change type Rebuild the table ALTER COLUMN โ€ฆ TYPE MODIFY COLUMN ALTER COLUMN
Set/drop NOT NULL Rebuild the table ALTER COLUMN โ€ฆ SET/DROP NOT NULL MODIFY COLUMN ALTER COLUMN
Add/drop constraint Rebuild the table ADD/DROP CONSTRAINT ADD/DROP CONSTRAINT ADD/DROP CONSTRAINT

Each ALTER TABLE statement in SQLite makes exactly one change. PostgreSQL and MySQL let you list several changes separated by commas.

Adding a column

ALTER TABLE โ€ฆ ADD COLUMN appends a new column to the end of the table. Existing rows get the column's default, or NULL if there's no default:

SQL Adding columns

Press Run (or Ctrl + Enter) to see the result here.

Ana and Ben now have no phone (NULL) and the tier basic; Chloe was inserted with both columns filled. In SQLite, adding a column is instant no matter how big the table is: it only rewrites the stored CREATE TABLE text, and rows written before the change return the default when read.

Because old rows aren't rewritten, SQLite has some limits on new columns:

  • It can't be a PRIMARY KEY or UNIQUE. (Add a unique index afterward instead.)
  • Its default must be a constant: no CURRENT_TIMESTAMP and no expression in parentheses.
  • If it's NOT NULL, it needs a non-NULL default, because the existing rows need some value.
  • A generated column can be added only as VIRTUAL, not STORED.

This example fails on purpose to show the NOT NULL rule:

SQL A NOT NULL column needs a default

Press Run (or Ctrl + Enter) to see the result here.

The error is Cannot add a NOT NULL column with default value NULL. The fix in real projects is a three-step change: add the column as nullable, fill it in for existing rows with an UPDATE, then make it NOT NULL (in SQLite, by rebuilding the table, shown below).

Renaming tables and columns

RENAME TO gives a table a new name, and RENAME COLUMN renames one column. Modern SQLite also updates every reference to the old name in indexes, triggers, views and foreign keys of other tables, so nothing breaks:

SQL Renames update references

Press Run (or Ctrl + Enter) to see the result here.

Look at the stored SQL: the invoices foreign key now says REFERENCES "customers"(id), and the view now reads SELECT id, name FROM "customers". One thing SQLite can't fix for you is your application code: every query that still says clients or full_name must change at the same time. That's the real cost of a rename, and why good names are worth choosing up front.

Dropping a column

DROP COLUMN (SQLite 3.35+) removes a column and its data from every row. Unlike adding a column, this rewrites the whole table:

SQL Dropping a column

Press Run (or Ctrl + Enter) to see the result here.

SQLite refuses to drop a column that something else depends on: the primary key, a UNIQUE column, a column used in an index, a foreign key, a generated column's expression, a table-level CHECK, a view or a trigger. For example, dropping id above fails with cannot drop PRIMARY KEY column: "id". Drop the index or view first, or rebuild the table.

Dropping is permanent. There is no undo for DROP COLUMN or DROP TABLE outside a transaction, and in MySQL, DDL statements commit automatically, so even a transaction won't save you. Take a backup before destructive schema changes, and in production, stop using a column in the code first, deploy, and only then drop it.

Rebuilding a table in SQLite

For anything ALTER TABLE can't do in SQLite (changing a type, adding a CHECK or foreign key, making a column NOT NULL, reordering columns) you rebuild the table: create a new table with the shape you want, copy the data across, drop the old one and rename the new one. Doing it inside a transaction means it either fully happens or doesn't happen at all.

Say the products table stored prices as text and allowed negative stock, and you want a proper INTEGER price, a CHECK on stock and a NOT NULL category:

SQL The rebuild recipe

Press Run (or Ctrl + Enter) to see the result here.

The rows keep their ids, prices are now exact integer cents, and the new constraints protect every future write. A few points about the recipe:

  • Turn foreign keys off first. Dropping products would otherwise fail (or cascade) if other tables reference it. Remember that PRAGMA foreign_keys is ignored inside a transaction.
  • Dropping the old table drops its indexes and triggers, so you must recreate them. List them first with SELECT sql FROM sqlite_schema WHERE tbl_name = 'products';.
  • Run PRAGMA foreign_key_check before committing. If it returns rows, the new table broke a reference, and you can ROLLBACK.
  • If the copy fails halfway, for example because an old row violates a new CHECK, the error stops the transaction and nothing changes. Fix the data first with an UPDATE.

Tools like Laravel's schema builder, Django and Rails do exactly this behind the scenes when you ask them to change a column in SQLite.

Other databases (read-only)

Server databases can change columns in place. Here's the same kind of change in each dialect:

SQL
-- PostgreSQL
ALTER TABLE products
  ALTER COLUMN price TYPE integer USING round(price::numeric * 100)::integer,
  ALTER COLUMN name SET NOT NULL,
  ADD CONSTRAINT stock_not_negative CHECK (stock >= 0);

-- MySQL 8
ALTER TABLE products
  MODIFY COLUMN name VARCHAR(200) NOT NULL,
  ADD CONSTRAINT stock_not_negative CHECK (stock >= 0);

-- SQL Server
ALTER TABLE products ALTER COLUMN name NVARCHAR(200) NOT NULL;
EXEC sp_rename 'products.legacy_code', 'old_code', 'COLUMN';

Be careful on big tables: some changes (like a type change in PostgreSQL, or many changes in older MySQL versions) rewrite the whole table and lock it while they run. On a table with millions of rows that can mean minutes of downtime. Teams avoid that with the "expand and contract" pattern: add the new column, copy data in batches, switch the code over, then drop the old column.

DROP TABLE

DROP TABLE deletes a table: its structure, all of its rows, and its indexes and triggers. Add IF EXISTS to skip the error when the table isn't there, which is handy in setup scripts. This example fails on purpose at the end:

SQL DROP TABLE and IF EXISTS

Press Run (or Ctrl + Enter) to see the result here.

The final query fails with no such table: temp_import. A few related facts:

  • With PRAGMA foreign_keys = ON, dropping a parent table that other rows still reference fails with FOREIGN KEY constraint failed (SQLite deletes the rows first, and the delete breaks the references). PostgreSQL refuses unless you write DROP TABLE โ€ฆ CASCADE, which also drops the dependent foreign key constraints.
  • Views that select from a dropped table remain in SQLite but fail when queried.
  • To empty a table but keep it, use DELETE FROM table; in SQLite or TRUNCATE TABLE table; in PostgreSQL, MySQL and SQL Server. See DELETE.

Migrations

In a real project you never type ALTER TABLE into a production console and hope. Instead, every schema change is written as a migration: a small, numbered script stored in version control alongside the code.

TEXT
migrations/
  0001_create_customers.sql
  0002_create_orders.sql
  0003_add_phone_to_customers.sql
  0004_rebuild_products_with_cents.sql

The database remembers which migrations it has already run, and a tool applies the missing ones in order, on every developer's machine, in CI and in production. That way all environments end up with exactly the same schema, and you can see in the history when and why each column appeared. Many tools also let each migration define a "down" step that reverses it.

Popular tools include Laravel migrations, Rails Active Record migrations, Django migrations, Alembic (Python), Flyway and Liquibase (Java), and Prisma Migrate (JavaScript). SQLite even has a built-in slot for a schema version number, PRAGMA user_version, which simple apps use to track which migrations have run:

SQL Tracking a schema version

Press Run (or Ctrl + Enter) to see the result here.

An app reads user_version on startup and runs every migration with a higher number. Good migration habits: one logical change per migration, never edit a migration that has already run in production (write a new one), and test migrations against a copy of real data before deploying. You'll find more in Backups, Migrations and Maintenance.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A SQLite table already has rows. Which statement fails?

    Existing rows need a value for a NOT NULL column, so SQLite requires a non-NULL default. The others are allowed.
  2. 2. How do you change a column's type in SQLite?

    SQLite's ALTER TABLE can't change types or constraints. The rebuild recipe does it safely inside a transaction. The other two are PostgreSQL and MySQL syntax.
  3. 3. After ALTER TABLE clients RENAME TO customers; in modern SQLite, what happens to a view defined as SELECT * FROM clients?

    SQLite rewrites references in views, triggers, indexes and foreign keys when you rename a table or column. Application code still has to be updated by you.
  4. 4. What does DROP TABLE IF EXISTS archive; do when there is no table called archive?

    IF EXISTS turns the missing-table error into a no-op, which makes setup scripts safe to run repeatedly.
  5. 5. Why are schema changes written as migrations in version control?

    Migrations keep development, testing and production schemas identical and record when and why each change happened.

Summary

  • ALTER TABLE changes an existing table without losing its rows; SQLite supports RENAME TO, RENAME COLUMN, ADD COLUMN and DROP COLUMN, one change per statement.
  • New SQLite columns can't be PRIMARY KEY or UNIQUE, need constant defaults, and need a non-NULL default if they're NOT NULL.
  • Renames update indexes, views, triggers and foreign keys inside the database, but not your application code.
  • For other changes in SQLite, rebuild the table: foreign keys off, BEGIN, create, copy, drop, rename, recreate indexes, check, COMMIT.
  • PostgreSQL, MySQL and SQL Server change columns in place with ALTER COLUMN / MODIFY COLUMN; big tables may be rewritten and locked.
  • DROP TABLE [IF EXISTS] removes a table with its data, indexes and triggers.
  • Teams apply schema changes as versioned migrations.

Next: Indexes, where you'll make queries on large tables fast.