SQL Tutorial · Changing Data and Tables · Lesson 31 of 36

SQL ALTER and DROP

Change a table's structure and remove tables safely.

ALTER TABLE

Add, rename or drop columns on an existing table:

Example 1Try it yourself

ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers RENAME COLUMN city TO town;
UPDATE customers SET phone = '+44 20 7946 0000' WHERE customer_id = 1;
SELECT name, town, phone FROM customers WHERE customer_id <= 2;

Output

name | town | phone
Ada Lovelace | London | +44 20 7946 0000
Alan Turing | Manchester | NULL

DROP TABLE

DROP TABLE deletes the table and all its data. IF EXISTS avoids an error when it's already gone:

Example 2Try it yourself

CREATE TABLE temp_import (line TEXT);
DROP TABLE IF EXISTS temp_import;
DROP TABLE IF EXISTS temp_import;
SELECT COUNT(*) AS tables_named_temp FROM sqlite_master WHERE name = 'temp_import';

Output

tables_named_temp
0

Migrations

In real projects, schema changes are written as migration files (Alembic, Flyway, Laravel migrations…) that run in order on every environment. Be careful with changes on large tables in production — some operations lock the table while they run.

Test yourself

What does DROP TABLE do to the table's data?

Keeps it in a backup
Deletes it along with the table
Moves it to another table
Nothing

Show answer

Answer: Deletes it along with the table