Tables are not carved in stone
Requirements change. A shop starts selling a product that needs a colour. A column was named badly. A table turns out to be unnecessary. After this lesson you will be able to change a table in place with ALTER TABLE, remove one with DROP TABLE, and cope with the fact that every database supports a different slice of ALTER TABLE.
The examples run in a scratch SQLite 3.54 database on new tables. Because SQLite does less here than PostgreSQL, MySQL or SQL Server, the lesson shows both what works and what does not, and gives the portable alternative.
Start with a small table
CREATE TABLE gadgets (
id INTEGER PRIMARY KEY,
name VARCHAR(30) NOT NULL,
price INTEGER NOT NULL
);
INSERT INTO gadgets VALUES (1, 'Cable', 200), (2, 'Charger', 900);Adding a column
ALTER TABLE ... ADD COLUMN adds a column to every existing row. In PostgreSQL, MySQL and SQLite it is written ADD COLUMN, and SQL Server uses ADD without the word COLUMN.
ALTER TABLE gadgets ADD COLUMN stock INTEGER NOT NULL DEFAULT 0;
SELECT * FROM gadgets;+----+---------+-------+-------+
| id | name | price | stock |
+----+---------+-------+-------+
| 1 | Cable | 200 | 0 |
| 2 | Charger | 900 | 0 |
+----+---------+-------+-------+Both existing rows received the default of 0. Now think about what would happen without one. The new column must be NOT NULL, but the existing rows have nothing to put in it:
ALTER TABLE gadgets ADD COLUMN required VARCHAR(20) NOT NULL;Error: Cannot add a NOT NULL column with default value NULLThe same problem exists in every database, although the message and sometimes the behaviour differ. The usual fix is to add the column with a default, or to add it as nullable, fill it in with an UPDATE, and only then make it required, in databases that can change nullability afterwards.
A nullable column is always safe to add:
ALTER TABLE gadgets ADD COLUMN colour VARCHAR(20);
SELECT * FROM gadgets;+----+---------+-------+-------+--------+
| id | name | price | stock | colour |
+----+---------+-------+-------+--------+
| 1 | Cable | 200 | 0 | NULL |
| 2 | Charger | 900 | 0 | NULL |
+----+---------+-------+-------+--------+Renaming and dropping columns
Renaming a column, and renaming a whole table, are simple in SQLite, MySQL 8 and PostgreSQL:
ALTER TABLE gadgets RENAME COLUMN name TO title;
ALTER TABLE gadgets RENAME TO products;
SELECT * FROM products;+----+---------+-------+-------+--------+
| id | title | price | stock | colour |
+----+---------+-------+-------+--------+
| 1 | Cable | 200 | 0 | NULL |
| 2 | Charger | 900 | 0 | NULL |
+----+---------+-------+-------+--------+SQL Server has no RENAME in ALTER TABLE. It uses a stored procedure, sp_rename, instead. Also note that renaming does not rewrite your other code. Any query, view or application that used the old name will break, so search before you rename.
Dropping a column removes it and its data for good:
ALTER TABLE products DROP COLUMN colour;
PRAGMA table_info(products);+-----+-------+-------------+---------+------------+----+
| cid | name | type | notnull | dflt_value | pk |
+-----+-------+-------------+---------+------------+----+
| 0 | id | INTEGER | 0 | NULL | 1 |
| 1 | title | VARCHAR(30) | 1 | NULL | 0 |
| 2 | price | INTEGER | 1 | NULL | 0 |
| 3 | stock | INTEGER | 1 | 0 | 0 |
+-----+-------+-------------+---------+------------+----+DROP COLUMN does not ask for confirmation, and there is no undo. A column that is part of a key, an index or a constraint usually cannot be dropped until that dependency is removed.
Changing a column's type, and what SQLite cannot do
Here the databases genuinely disagree. Each of these changes the price column's type. They are shown, not run, because SQLite rejects every one of them:
-- PostgreSQL
ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(8,2);
-- MySQL
ALTER TABLE products MODIFY COLUMN price DECIMAL(8,2) NOT NULL;
-- SQL Server
ALTER TABLE products ALTER COLUMN price DECIMAL(8,2) NOT NULL;SQLite has no statement to change a column's type, and, in the version used here (3.54), several other changes are not available either. For example, adding a UNIQUE column is refused:
ALTER TABLE products ADD COLUMN code INTEGER UNIQUE;Error: Cannot add a UNIQUE columnSupport has been growing in recent SQLite releases. In 3.54 it is possible to add and drop a named CHECK constraint on an existing table, and the database validates the rows already there when you add one:
ALTER TABLE products ADD CONSTRAINT ck_price CHECK (price > 0);Here this succeeds. But if a row already broke the rule, the statement would fail with the message constraint failed, and the rule would not be added. Check your own SQLite version before relying on this, because older releases do not accept it.
The portable fallback: rebuild the table
When the database cannot make the change directly, the dependable method works everywhere. Create the table you want, copy the rows across, drop the old table and rename the new one:
CREATE TABLE products_new (
id INTEGER PRIMARY KEY,
title VARCHAR(30) NOT NULL UNIQUE,
price DECIMAL(8,2) NOT NULL
);
INSERT INTO products_new (id, title, price)
SELECT id, title, price FROM products;
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;
SELECT * FROM products;+----+---------+-------+
| id | title | price |
+----+---------+-------+
| 1 | Cable | 200 |
| 2 | Charger | 900 |
+----+---------+-------+The new table has a unique title that the old one did not. If your database supports transactions for this kind of change, and SQLite does, wrap the steps in one transaction so that a failure halfway leaves the original intact. A rebuild also loses anything attached to the old table, such as indexes and foreign keys in other tables, so recreate them afterwards.
DROP TABLE
DROP TABLE removes a table, its data, and its indexes. It is permanent. If you drop a table that does not exist you get an error, so scripts use IF EXISTS:
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS products;
SELECT 'finished' AS status;+----------+
| status |
+----------+
| finished |
+----------+The second drop did nothing, and the script carried on. Without IF EXISTS, SQLite reports no such table: products.
A table that other tables refer to is protected. With foreign key enforcement switched on in SQLite, dropping a parent table whose child still holds rows is refused:
DROP TABLE customers;Error: FOREIGN KEY constraint failedThis assumes customers is referenced by an invoices table that has rows, as in the previous lesson's exercise, with PRAGMA foreign_keys = ON. Other databases refuse to drop a referenced table too, but they word it differently, and PostgreSQL offers DROP TABLE ... CASCADE to remove the dependent foreign keys along with it. Use that with great care.
Remember the three ways to get rid of data: DELETE removes chosen rows, TRUNCATE (where supported) removes all rows and keeps the table, and DROP TABLE removes the table itself.
For MySQL specifics see the MySQL ALTER TABLE lesson.
Common mistakes
- Dropping the wrong table, or the right table in the wrong database. There is no undo. Take a backup or work in a transaction first, and read the table name twice.
- Adding a required column without a default. It fails on any table that already has rows.
- Renaming something that other code still uses. Views, queries and applications are not updated for you.
- Assuming
ALTERworks the same everywhere. The type-change statements above differ in all three major databases, and SQLite supports fewer forms than they do. - Testing a migration only on an empty table. Constraints that pass on no rows may fail on real data.
Exercise
Starting from a table tasks with an integer id primary key and a required title, add an integer column priority that is required and defaults to 3, and then insert a task without a priority and select all the rows.
Show one possible answer
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
title VARCHAR(80) NOT NULL
);
INSERT INTO tasks (title) VALUES ('Write report');
ALTER TABLE tasks ADD COLUMN priority INTEGER NOT NULL DEFAULT 3;
INSERT INTO tasks (title) VALUES ('Book tickets');
SELECT * FROM tasks;+----+--------------+----------+
| id | title | priority |
+----+--------------+----------+
| 1 | Write report | 3 |
| 2 | Book tickets | 3 |
+----+--------------+----------+The task that existed before the change and the one added after both received priority 3, the first from the default applied during ALTER TABLE and the second from the normal default on insert.
Key points
- ALTER TABLE changes a table's structure without losing its rows: add, rename or drop columns, or rename the table.
- Adding a NOT NULL column to a table that already has rows needs a default, because the existing rows must get some value.
- DROP TABLE deletes the table and all its data permanently. DROP TABLE IF EXISTS makes a script safe to re-run.
- ALTER support differs a lot between databases. SQLite can do less, and the fallback is to create a new table, copy the rows, drop the old table and rename.