Sometimes you need to change the structure of an existing table. The ALTER TABLE command lets you modify tables after they’ve been created, without losing the data that’s already in them.

This is different from CREATE TABLE, which creates brand new tables. ALTER TABLE modifies existing tables.

Adding a Column

A common use of ALTER TABLE is adding new columns to existing tables.

Let’s add a created_at column to track when each user was created:

ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

What this does:

  • ALTER TABLE users = modify the users table
  • ADD COLUMN created_at = add a new column called created_at
  • TIMESTAMP = stores date and time
  • DEFAULT CURRENT_TIMESTAMP = automatically sets the current date/time when a row is created

Important: Existing data remains unaffected. All your current users stay in the table - they just get a new created_at field with the current timestamp.

Check Your Changes

After running the ALTER TABLE command, you can verify the change:

DESCRIBE users;

You’ll now see your table has a created_at column in addition to id, username, and email.

Other ALTER TABLE Operations

ALTER TABLE can also:

  • Drop columns: ALTER TABLE users DROP COLUMN created_at;
  • Modify columns: ALTER TABLE users MODIFY COLUMN username VARCHAR(150);
  • Rename columns: ALTER TABLE users RENAME COLUMN username TO user_name;

Tags: