Lesson 22 of 26 · see the practice database

Changing tables, dropping them, and indexes

Tables change over time. The business wants to record something new, a column was badly named, an old table is no longer needed. ALTER TABLE changes the structure of a table without touching the rest of its data. DROP TABLE removes a table. This lesson covers both, and then indexes, the main tool for making queries fast.

Adding a column

ALTER TABLE customers ADD COLUMN phone TEXT;

SELECT id, name, phone FROM customers LIMIT 3;

The new column exists in every row, and it is empty: all the existing rows hold NULL in it. Fill it with UPDATE.

UPDATE customers SET phone = '98765 00001' WHERE id = 1;

Give a default, and existing rows get that value instead of NULL.

ALTER TABLE products ADD COLUMN active INTEGER NOT NULL DEFAULT 1;

SELECT id, name, active FROM products LIMIT 3;

A new column that is NOT NULL needs a default. Otherwise the database would not know what to put in the rows that already exist.

Renaming

ALTER TABLE customers RENAME COLUMN phone TO mobile;

ALTER TABLE employees RENAME TO staff;

SELECT name, department FROM staff LIMIT 2;

Renaming is quick for the database and can be slow for people: every saved query, report and program that used the old name stops working. On a shared database, agree a rename with everyone who uses the table first. We rename the table back so that later examples work.

ALTER TABLE staff RENAME TO employees;

Removing a column

ALTER TABLE customers DROP COLUMN mobile;

The column and everything in it are gone for good. Check first that nothing still uses it.

What ALTER TABLE can do differs by database

This is one of the least uniform parts of SQL.

SQLite cannot change the type of a column or add most constraints to an existing table. The usual way round is to create a new table with the right design, copy the data across with INSERT ... SELECT, drop the old table and rename the new one.

DROP TABLE

DROP TABLE deletes a table: its structure, its data, everything.

CREATE TABLE scratch (note TEXT);

DROP TABLE scratch;

SELECT * FROM scratch;

Error: no such table: scratch

There is no confirmation and, outside a transaction, no undo. Dropping a table that does not exist is an error; DROP TABLE IF EXISTS name avoids that in scripts.

DELETE, TRUNCATE or DROP?

Interviewers like this question. DELETE is a data statement and can be filtered. TRUNCATE and DROP are structure statements and cannot.

Indexes: why some searches are fast

Think of the index at the back of a textbook. To find every page that mentions "photosynthesis", you do not read the whole book. You look the word up in the index, which is in alphabetical order, and go straight to the pages.

A database index works the same way. Without one, a search such as WHERE city = 'Delhi' makes the database read every row of the table. That is called a full table scan. With an index on city, it can jump to the matching rows. On twelve rows nobody can tell the difference. On twelve million, it is the difference between a blink and a long wait.

Creating an index

CREATE INDEX idx_customers_city ON customers (city);

Give the index a name, the table, and the column. Nothing looks different afterwards. Queries are written exactly as before and return exactly the same rows. The database simply uses the index when it helps.

SELECT name, city FROM customers WHERE city = 'Delhi';

An index can cover two columns. The order matters: an index on (status, order_date) helps searches on status alone, or on status and date together, but not on date alone, in the same way that a phone book sorted by surname and then first name is no help in finding everyone called Ravi.

CREATE INDEX idx_orders_status_date ON orders (status, order_date);

A unique index also enforces that no two rows share a value.

CREATE UNIQUE INDEX idx_products_name ON products (name);

What to index

• Primary keys are indexed automatically. You never need to do it.
• Foreign key columns, because joins search on them: orders.customer_id, order_items.order_id, order_items.product_id.
• Columns that appear often in WHERE.
• Columns used often in ORDER BY.

What indexes cost

Indexes are not free, which is why you do not index everything.

• Each index takes disk space.
• Every INSERT, UPDATE and DELETE must update the indexes as well as the table, so writing gets slower as indexes are added.
• An index on a column with only a few different values, such as a yes or no flag, rarely helps.
• As earlier lessons noted, an index on a column is usually ignored when the query wraps the column in a function, or when a LIKE pattern starts with %.

The sensible approach is to add indexes for the queries you really run, and to measure.

Asking the database what it will do

Every database can explain how it plans to run a query. In SQLite, put EXPLAIN QUERY PLAN in front.

EXPLAIN QUERY PLAN
SELECT name FROM customers WHERE city = 'Delhi';

The answer mentions the index made above, which shows it is being used. MySQL and PostgreSQL use the word EXPLAIN alone. Reading plans is a skill of its own. For now it is enough to know that the tool exists and that the word "scan" on a big table is the thing to look for.

Removing an index

DROP INDEX idx_products_name;

Dropping an index loses no data. Only the speed-up goes. MySQL and SQL Server need the table name too: DROP INDEX idx_products_name ON products.

Try it yourself

In the practice box, press Reset the database first.

ALTER TABLE orders ADD COLUMN notes TEXT;

CREATE INDEX idx_products_category ON products (category);

CREATE TABLE temp_list (item TEXT);
DROP TABLE temp_list;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.