Altering Schema

The previous lesson wrote the schema together with its constraints. A real schema does not stay in the shape it was first written: members need a phone field, a column’s name turns out to have been chosen badly, a constraint gets added to the email field after the fact — and these changes happen while the table already holds data, not while it is empty.

The ALTER TABLE statement writes these changes. The statement itself is standard, but which change it can make in a single statement differs by engine — one of SQL’s weakest spots for portability. This lesson shows the statement and its limit, then sets up a way past that limit that works on every engine.

Adding and Renaming Columns

The two most commonly needed changes are also the cheapest: adding a column at the end, and changing a column’s name. Both can be done without touching the content of existing rows.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE,
  registered_at TEXT NOT NULL
);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
                       (3,'Clara','Diaz',NULL,'2024-01-09');

ALTER TABLE member ADD COLUMN phone TEXT;
ALTER TABLE member ADD COLUMN notification TEXT NOT NULL DEFAULT 'email';
ALTER TABLE member RENAME COLUMN registered_at TO membership_date;

SELECT * FROM member;
SQL

member_id  first_name  last_name  email               membership_date  phone  notification
---------  ----------  ---------  ------------------  ---------------  -----  ------------
1          Alice       Kane       [email protected]  2023-02-14              email       
3          Clara       Diaz                           2024-01-09              email       

Both existing rows picked up the new columns. The phone column has no value, because neither a constraint nor a default was written for it: old rows carry a null. The notification column, on the other hand, has a NOT NULL constraint, so a blank cannot be accepted — the engine filled the old rows with the default value. When adding a column, one of these two paths has to be chosen: either the column is allowed to stay blank, or what old rows get is written explicitly.

RENAME COLUMN changes only the name; it does not touch the data or the constraints. This looks harmless but has a wide-reaching effect: every query, every view, and every piece of application code that uses the column’s old name breaks. Fixing a naming mistake is cheap when it is caught early; it is not cheap months later.

Each block in this lesson runs on its own and sets up only the columns the member table needs for that particular example; the course’s actual schema is the definition written in the previous lesson. The rename above is not carried into that definition either — it is there only to show its effect. In the lessons that follow, the column keeps the name registered_at.

The Limits of Adding

Adding a column is not always cheap. It is cheap because the engine does not have to touch existing rows. There are three situations where that condition breaks down, and the engine rejects all three.

sqlite3 :memory: <<'SQL'
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE,
  registered_at TEXT NOT NULL
);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14');

ALTER TABLE member ADD COLUMN identity_no TEXT NOT NULL;
ALTER TABLE member ADD COLUMN card_no TEXT UNIQUE;
ALTER TABLE member ADD COLUMN last_visit TEXT DEFAULT (date('now'));
SQL

Runtime error near line 10: Cannot add a NOT NULL column with default value NULL
Parse error near line 11: Cannot add a UNIQUE column
Runtime error near line 12: Cannot add a column with non-constant default

The three rejections come with three separate reasons. The first is a mandatory column with no default: what an existing row would hold in that column is undefined, and it cannot be left blank either. The second is a uniqueness constraint: since the new column would carry the same value — a blank or a default — in every row, the constraint would be violated the moment it is set up. The third is a non-constant default: a default whose value is computed separately for each row leaves what gets written to old rows undefined.

The wording of these three messages is engine-specific. The rule that carries over is this: an addition that requires existing rows to be rewritten cannot be done in a single statement. The column is first added in a form that can stay blank, the rows are filled in, and the constraint is set up afterward.

Dropping a Column

Dropping a column runs into a similar limit. The operation itself is well defined, but it is rejected if the column being dropped is part of a constraint or an index.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE,
  registered_at TEXT NOT NULL,
  phone             TEXT
);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14','0312 000 00 00');

ALTER TABLE member DROP COLUMN phone;
SELECT * FROM member;
ALTER TABLE member DROP COLUMN email;
SQL

Parse error near line 15: cannot drop UNIQUE column: "email"
member_id  first_name  last_name  email               registered_at
---------  ----------  ---------  ------------------  -------------
1          Alice       Kane       [email protected]  2023-02-14   

The unconstrained phone column came off; email, which carries a unique constraint, did not. Dropping a column is also irreversible: every value it held goes with it. On a production table, the work that comes first is to stop using the column and wait; dropping it is the last step.

Renaming a Table

When a table’s name changes, what happens to the foreign keys referencing it is a separate question. The engine used here tracks and updates those references.

sqlite3 :memory: <<'SQL'
PRAGMA foreign_keys = ON;

CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (
  book_id   INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  author    TEXT NOT NULL,
  branch_id INTEGER REFERENCES branch(branch_id)
);

ALTER TABLE branch RENAME TO branches;
.schema book
SQL

CREATE TABLE book (
  book_id   INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  author    TEXT NOT NULL,
  branch_id INTEGER REFERENCES "branches"(branch_id)
);

The definition of the book table changed even though it was never touched directly. This behavior differs by engine: some track the reference, others keep holding the old name and leave the schema inconsistent. Before renaming a table, it has to be worked out who references it.

Rebuilding a Table

Changing a column’s type, adding a constraint to an existing column, removing a constraint, or fixing the order of columns — none of these can be done in a single statement on the engine used here. Some engines can do part of this, but even where it is possible, the operation rewrites rows if the table is large.

The path that works on every engine is rebuilding the table, in four steps: create a new table with the wanted definition, copy the data over, drop the old table, and give the new table the old name. All four happen inside a single transaction, so a step left half-done does not leave the schema broken.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
PRAGMA foreign_keys = OFF;

CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE,
  registered_at TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'active'
                 CHECK (status IN ('active', 'suspended', 'closed')),
  notification      TEXT NOT NULL DEFAULT 'email'
);
INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES
  (1,'Alice','Kane','[email protected]','2023-02-14'),
  (3,'Clara','Diaz',NULL,'2024-01-09');

BEGIN;

CREATE TABLE member_new (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE CHECK (email IS NULL OR email LIKE '%@%'),
  registered_at TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'active'
                 CHECK (status IN ('active', 'suspended', 'closed')),
  notification      TEXT NOT NULL DEFAULT 'email'
                 CHECK (notification IN ('email', 'sms', 'none'))
);

INSERT INTO member_new (member_id, first_name, last_name, email, registered_at, status, notification)
SELECT member_id, first_name, last_name, email, registered_at, status, notification FROM member;

DROP TABLE member;
ALTER TABLE member_new RENAME TO member;

COMMIT;

PRAGMA foreign_key_check;
SELECT * FROM member;
SQL

member_id  first_name  last_name  email               registered_at  status  notification
---------  ----------  ---------  ------------------  -------------  ------  ------------
1          Alice       Kane       [email protected]  2023-02-14     active  email       
3          Clara       Diaz                           2024-01-09     active  email       

Neither of the two check conditions in the new definition could have been added with ALTER TABLE, because both attach to existing columns: one to email, the other to the notification column added at the start of the lesson. That column could be given a default when it was added, but not a constraint; the constraint only enters the definition here, while the table is being rebuilt. The data was preserved, and both rows fit the new definition — including the row with a blank email, because the condition handles the blank separately.

The copy step has a side benefit: because the SELECT list is written out, columns can be transformed. If a date column is being converted from text to a number, the conversion happens here; if an old column is being dropped, it is left out of the list entirely.

During the copy, the new definition’s constraints are enforced. If the old data violates the new constraint, the statement fails and the transaction rolls back — which is exactly what is wanted. A schema change is planned together with the work of bringing the data in line with the constraint.

The PRAGMA foreign_key_check call printed nothing: there is no broken reference. This call is the last step of the procedure, and it should not be skipped.

The Risk in Rebuilding

Foreign key checking is turned off for the whole procedure, because between the DROP TABLE step and the RENAME TO step the target table is temporarily absent. Having the check off means a mistake made during the copy passes silently.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
PRAGMA foreign_keys = OFF;

CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (
  book_id   INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  branch_id INTEGER NOT NULL REFERENCES branch(branch_id)
);
INSERT INTO branch VALUES (1,'Central','Ankara'),(3,'Kadikoy','Istanbul');
INSERT INTO book VALUES (1,'Blindness',1),(5,'Silent House',3);

BEGIN;
CREATE TABLE branch_new (
  branch_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  city      TEXT NOT NULL,
  is_open   INTEGER NOT NULL DEFAULT 1
);
INSERT INTO branch_new (branch_id, name, city)
SELECT branch_id, name, city FROM branch WHERE city = 'Ankara';
DROP TABLE branch;
ALTER TABLE branch_new RENAME TO branch;
COMMIT;

PRAGMA foreign_key_check;
SQL

table  rowid  parent  fkid
-----  -----  ------  ----
book   5      branch  0   

Because of the condition in the copy statement, one branch was not carried over, and the book tied to it was left orphaned. The transaction committed successfully, with no error raised; only the check call revealed the break. This last step of the procedure is the only warning against silent data loss.

In practice, three habits cut down this risk. The rebuild procedure is tried first on a copy of the production data. Row counts are compared after the copy statement. And the procedure is run inside a transaction, so that if one of the steps fails, the schema returns to its previous state.

Summary

• ALTER TABLE adds, renames, and drops columns in a single statement; which of these changes is supported differs by engine.
• Additions that would require existing rows to be rewritten are rejected: a mandatory column with no default, a unique column, and a non-constant default.
• Dropping a column is irreversible and is rejected for columns that are part of a constraint; the safe order is to stop using it first and drop it afterward.
• For changes that cannot be made in a single statement, the portable path is to rebuild the table: new table, copy, drop, rename — all inside a single transaction.
• Because foreign key checking is off during a rebuild, the last step of the procedure is testing referential integrity.

Next Step

The schema is set up and can be changed. The next question is how data goes into it. The next lesson takes up inserting rows: single-row inserts, multiple rows in a single statement, writing a query’s result straight into a table, and the point where these diverge — performance. Whether twenty thousand rows are inserted one at a time or inside a single transaction is a measurable difference.