Modifying Tables with ALTER TABLE

Written with a little help from AI, reviewed and edited by a human.

When you first create a table, you define its structure — what columns it has, what types they are, etc. But requirements change. Maybe you need to add a new column, rename an existing one, or change a constraint. ALTER TABLE lets you modify an existing table without having to delete it and start over.

Real life example

Imagine you’re building a filing cabinet for student records. At first you only need sections for “Name” and “Email”. Later, you realize you also need a section for “Phone Number”. You don’t throw away the entire cabinet and rebuild it — you just add a new drawer or section. That’s what ALTER TABLE does.

Adding a column

The most common use of ALTER TABLE is adding a new column.

Let’s say you have a users table:

id	name	email
1	Alice	alice@example.com
2	Bob	bob@example.com

Now you realize you need to store phone numbers. Instead of recreating the table, you can add the column:

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

Now the table looks like this:

id	name	email	phone
1	Alice	alice@example.com	NULL
2	Bob	bob@example.com	NULL

The new column is added with NULL values for all existing rows because those users didn’t have phone numbers before.

You can also add a column with a default value:

ALTER TABLE users
ADD COLUMN status VARCHAR(20) DEFAULT 'active';

Now all existing users will have status = 'active' instead of NULL.

Dropping a column

If you no longer need a column, you can remove it:

ALTER TABLE users
DROP COLUMN phone;

Warning: This permanently deletes all data in that column. There’s no undo. Make sure you really don’t need it, and back up your data first if you’re unsure.

Renaming a column

If you want to change a column’s name:

ALTER TABLE users
RENAME COLUMN email TO email_address;

The data stays the same — only the column name changes.

Renaming a table

You can also rename the entire table:

ALTER TABLE users
RENAME TO customers;

Now the table is called customers instead of users.

Modifying a column’s type or constraints

Changing a column’s data type or constraints is trickier and depends on your database system.

In some databases (like PostgreSQL), you can do:

ALTER TABLE users
ALTER COLUMN phone TYPE VARCHAR(50);

But in SQLite, you cannot directly modify a column. Instead, you have to:

Create a new table with the correct structure
Copy the data over
Drop the old table
Rename the new table

This is cumbersome, which is why it’s important to think carefully about your table design before creating it.

Adding constraints

You can add constraints to existing tables:

Adding a UNIQUE constraint

ALTER TABLE users
ADD UNIQUE (email);

Now email must be unique — no two users can have the same email.

Adding a NOT NULL constraint

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

Now email cannot be NULL. If you have existing rows with NULL emails, this will fail unless you update them first.

Adding a foreign key

ALTER TABLE posts
ADD FOREIGN KEY (user_id) REFERENCES users(id);

This creates a relationship between posts.user_id and users.id.

Dropping constraints

Removing constraints is also database-specific. In many databases:

ALTER TABLE users
DROP CONSTRAINT constraint_name;

You’ll need to know the name of the constraint, which you can find by looking at the table’s schema.

When to use ALTER TABLE

Use ALTER TABLE when:

You need to add a new feature and that requires a new column
You’re renaming things to make them clearer
You’re adding constraints for data integrity
You’re removing outdated columns

When NOT to use ALTER TABLE

Don’t use ALTER TABLE to:

Fix a fundamentally bad design (sometimes it’s better to recreate the table properly)
Change column types in production without careful planning (this can break existing data or queries)

Best practices

Back up your data first: Before making schema changes, especially in production, always back up your database.

Test on a copy: Try your ALTER TABLE commands on a test database first to make sure they work as expected.

Check for dependencies: If you’re dropping a column, make sure no other tables or code depend on it.

Use transactions: Wrap your changes in a transaction so you can roll back if something goes wrong:

BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Check that it looks right
COMMIT;  -- or ROLLBACK if there's a problem

Common SQLite limitations

SQLite is more limited than other databases when it comes to ALTER TABLE. You cannot:

Drop a column (in older versions; newer versions support this)
Modify a column’s data type
Add a constraint other than FOREIGN KEY

For these operations, you have to recreate the table. Other databases like PostgreSQL and MySQL are more flexible.

Summary

ALTER TABLE is your tool for evolving your database schema over time. You can add columns, rename things, and add constraints without losing data. Just be careful, especially in production, and always test your changes first.