ALTER TABLE: changing a table

In short: ALTER TABLE changes an existing table by adding, renaming or dropping columns, and DROP TABLE removes the whole table.

Problem

The courier calls

The courier arrives, and the customer does not open the door. The courier wants a phone number. There is no column for a phone number in customers.

The table cannot be made again: it holds 20 customers with orders, reviews and sign-up dates. It has to be changed in place.

The idea

ADD COLUMN

ALTER TABLE changes a table that already exists. ADD COLUMN adds a column, written as in CREATE TABLE:

SQLdatabase: shop

ALTER TABLE customers ADD COLUMN phone TEXT;
SELECT name, city, phone FROM customers WHERE id <= 3;

ALTER TABLE customers ADD COLUMN phone TEXT; SELECT name, city, phone FROM customers WHERE id <= 3;

▶ RunRestore the queryFresh database

The existing customers got NULL: their phone numbers are not known yet. The data in the other columns is not touched.

See it

A required column in a full table

Marketing wants a column “has the customer agreed to the newsletter”, and a required one:

SQLdatabase: shop

ALTER TABLE customers ADD COLUMN newsletter INTEGER NOT NULL;

ALTER TABLE customers ADD COLUMN newsletter INTEGER NOT NULL;

▶ RunRestore the queryFresh database

An error. The table already has 20 rows. If the column is NOT NULL, what should it hold for them? SQLite does not know, so it refuses.

With DEFAULT it knows:

SQLdatabase: shop

ALTER TABLE customers ADD COLUMN newsletter INTEGER NOT NULL DEFAULT 0;
SELECT name, newsletter FROM customers WHERE id <= 3;

ALTER TABLE customers ADD COLUMN newsletter INTEGER NOT NULL DEFAULT 0; SELECT name, newsletter FROM customers WHERE id <= 3;

▶ RunRestore the queryFresh database

All the existing customers got 0: they have not agreed until they say otherwise.

More precisely, if you are curious

SQLite has more limits on ADD COLUMN. The new column cannot be UNIQUE or PRIMARY KEY. If it has REFERENCES and the check is on, DEFAULT must be NULL. A CHECK on the new column is allowed: SQLite checks it against the existing rows too.

Try it

The unit

Products are sold by the piece, but soon some will be sold by the kilo. Add to products a column unit: text, required, with the default 'pcs'.

SQLdatabase: shop

ALTER TABLE products ADD COLUMN unit TEXT;

ALTER TABLE products ADD COLUMN unit TEXT;

Check▶ Run

The idea

RENAME COLUMN and DROP COLUMN

RENAME COLUMN changes a column's name. The data stays:

SQLdatabase: shop

ALTER TABLE customers RENAME COLUMN joined_at TO registered_at;
SELECT name, registered_at FROM customers WHERE id <= 3;

ALTER TABLE customers RENAME COLUMN joined_at TO registered_at; SELECT name, registered_at FROM customers WHERE id <= 3;

▶ RunRestore the queryFresh database

SQLite also changes the name everywhere else in the schema where the column is mentioned.

DROP COLUMN removes a column together with its data. The shop will never send a fax:

SQLdatabase: shop

ALTER TABLE customers ADD COLUMN fax TEXT;
ALTER TABLE customers DROP COLUMN fax;
SELECT * FROM customers WHERE id = 1;

ALTER TABLE customers ADD COLUMN fax TEXT; ALTER TABLE customers DROP COLUMN fax; SELECT * FROM customers WHERE id = 1;

▶ RunRestore the queryFresh database

DROP COLUMN has been in SQLite since version 3.35 (2021). It cannot remove a column the schema depends on: a primary key, a UNIQUE column, or a column used by an index or a view.

SQLdatabase: shop

ALTER TABLE customers DROP COLUMN email;

ALTER TABLE customers DROP COLUMN email;

▶ RunRestore the queryFresh database

The idea

What SQLite cannot do

The warehouse wants the stock never to be negative. That is CHECK (stock >= 0) on a column that already exists:

SQLdatabase: shop

ALTER TABLE products ADD CHECK (stock >= 0);

ALTER TABLE products ADD CHECK (stock >= 0);

▶ RunRestore the queryFresh database

SQLite cannot add a rule to an existing column, nor change its type. In SQLite, ALTER TABLE can only add, rename and remove columns, and rename the table.

For anything else you make a new table. Here is how reviews become required to have text:

SQLdatabase: shop

CREATE TABLE reviews_new (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id),
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  body TEXT NOT NULL,
  created_at TEXT NOT NULL
);
INSERT INTO reviews_new SELECT * FROM reviews;
DROP TABLE reviews;
ALTER TABLE reviews_new RENAME TO reviews;
SELECT name, "notnull" FROM pragma_table_info('reviews') WHERE name = 'body';

CREATE TABLE reviews_new ( id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL REFERENCES products(id), customer_id INTEGER NOT NULL REFERENCES customers(id), rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5), body TEXT NOT NULL, created_at TEXT NOT NULL ); INSERT INTO reviews_new SELECT * FROM reviews; DROP TABLE reviews; ALTER TABLE reviews_new RENAME TO reviews; SELECT name, "notnull" FROM pragma_table_info('reviews') WHERE name = 'body';

▶ RunRestore the queryFresh database

Four steps: a new table with the rule, copying the data with INSERT … SELECT, DROP TABLE on the old one and RENAME TO on the new one. If a review had no text, the copying would have stopped on NOT NULL. Then you fix the data first.

More precisely, if you are curious

PostgreSQL and MySQL can do more: ALTER TABLE products ADD CONSTRAINT stock_ok CHECK (stock >= 0) and changing a column's type. For SQLite the full procedure is in the documentation (“ALTER TABLE”, the section on changes it does not support directly). It also has steps for the old table's foreign keys, indexes and triggers. The reviews have none of those, so four steps are enough here.

The idea

DROP TABLE

DROP TABLE removes the whole table, with all its data. There is no undo. The promotion from 2025 ended long ago:

SQLdatabase: shop

DROP TABLE promo_2025;
DROP TABLE IF EXISTS promo_2025;
SELECT name FROM sqlite_schema WHERE type = 'table';

DROP TABLE promo_2025; DROP TABLE IF EXISTS promo_2025; SELECT name FROM sqlite_schema WHERE type = 'table';

▶ RunRestore the queryFresh database

sqlite_schema is SQLite's list of tables. promo_2025 is gone. The second line gives no error because of IF EXISTS. Without it, removing a table that is not there stops:

SQLdatabase: shop

DROP TABLE promo_2025;

DROP TABLE promo_2025;

▶ RunRestore the queryFresh database

A table that other tables point to cannot be removed like that. order_items and reviews point to products:

SQLdatabase: shop

DROP TABLE products;

DROP TABLE products;

▶ RunRestore the queryFresh database

DROP TABLE first deletes all the rows, and REFERENCES from the previous lesson does not allow it.

Exercise

Two changes

1. The couriers want a phone number and a delivery note (“bell 3, entrance B”).

Add to customers the columns phone and delivery_note, both text.

SQLdatabase: shop

ALTER TABLE customers ADD COLUMN phone TEXT;

ALTER TABLE customers ADD COLUMN phone TEXT;

Check▶ Run

2. In reviews the date is called created_at, and in products it is added_at. Things that are the same should have the same name.

Rename added_at in products to created_at.

SQLdatabase: shop

Check▶ Run

In a real project the schema is not changed by hand in a console, but with migrations: files with such ALTER TABLE statements, run in the same order on every computer and server. You will see them in the module on the database in a program.

Test

Check yourself

1. You add a column phone TEXT to a table with 20 customers. What is in it for the existing customers?

Empty textAn error: the column cannot be addedNULL

2. Why does ADD COLUMN newsletter INTEGER NOT NULL stop with an error?

Because the existing rows would be left with no valueBecause the column already existsBecause INTEGER cannot be NOT NULL

3. How do you add a CHECK to an existing column in SQLite?

ALTER TABLE … ALTER COLUMN …A new table with the rule, copying the data, DROP of the old one and RENAME of the new oneALTER TABLE … ADD CHECK (…)

4. What does DROP TABLE IF EXISTS promo_2025 do if the table is not there?

Nothing, with no errorA no such table errorIt makes an empty table

Check

Start the lesson over

Related lessons

• Builds on: INSERT: new rows · CREATE TABLE: a new table · Constraints: rules for the data · Foreign keys: links between tables
• Continues in: Views · Natural keys and denormalization · Indexes · Schema migrations
• Tasks on this topic: Tables and rules