How to add a column with a default value in SQL

1ALTER TABLE … ADD COLUMN … DEFAULTRecommended

Name the table, the new column, its type and the default. The rows already in the table get the default as soon as the statement runs, so there is no separate UPDATE to write. After that the default works like any other: an INSERT that leaves the column out gets it, and one that names the column keeps its own value, even when that value is NULL.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO users (name) VALUES ('Ana'), ('Ben'), ('Cleo');

-- Existing rows get the default straight away
ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
ALTER TABLE users ADD COLUMN credits INTEGER DEFAULT 10;

-- New rows get it too, unless the INSERT names the column
INSERT INTO users (name) VALUES ('Dev');
INSERT INTO users (name, status, credits) VALUES ('Eli', 'invited', 0);
INSERT INTO users (name, credits) VALUES ('Fay', NULL);

SELECT id, name, status, credits FROM users;

Ana, Ben and Cleo were inserted before either column existed, yet they show active and 10. Dev left both columns out and got the defaults. Eli gave his own values, so he is invited with 0 credits. Fay passed an explicit NULL and kept it: a default only applies when the column is missing from the insert, not when its value is NULL. SQLite adds one column per statement; to fill a new column from another table instead of a constant, add it without a default and see update from another table.

2A NOT NULL column needs a default

A new nullable column without a default is easy: the existing rows get NULL. A NOT NULL column is different, because the rows already in the table need some value that isn't NULL, and the default is where it comes from. Leave it out and SQLite refuses the change. The last statement below shows the error.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total INTEGER NOT NULL
);
INSERT INTO orders (total) VALUES (1999), (500);

-- A nullable column with no default: old rows get NULL
ALTER TABLE orders ADD COLUMN note TEXT;

-- NOT NULL with a default works: old rows get the default
ALTER TABLE orders ADD COLUMN currency TEXT NOT NULL DEFAULT 'EUR';

SELECT id, total, note, currency FROM orders;

-- NOT NULL with no default fails: what would the old rows hold?
ALTER TABLE orders ADD COLUMN paid INTEGER NOT NULL;

Both orders show NULL in note and EUR in currency. The paid column fails with Cannot add a NOT NULL column with default value NULL. PostgreSQL and SQL Server also refuse a NOT NULL column without a default once the table has rows; MySQL accepts it and fills the old rows with the type's implicit value (0 for an INT). If you want a required column with no default, add it as nullable, fill it with an UPDATE, then make it NOT NULL as in section 5. SQLite also rejects a UNIQUE or PRIMARY KEY column in ADD COLUMN, and a non-constant default such as CURRENT_TIMESTAMP once the table has rows.

3Rename a column: RENAME COLUMN

ALTER TABLE … RENAME COLUMN old TO new is the standard form, and SQLite (3.25+), PostgreSQL and MySQL 8.0+ all accept it. SQLite rewrites every index, view and trigger that mentions the column, so nothing that depended on the old name breaks. The code below renames a column that both an index and a view use, then reads from the view.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    nm TEXT NOT NULL,
    price INTEGER
);
INSERT INTO products (nm, price) VALUES ('Pen', 3), ('Mug', 9);
CREATE INDEX idx_products_nm ON products (nm);
CREATE VIEW cheap AS SELECT nm FROM products WHERE price < 5;

-- Rename the column (SQLite 3.25+)
ALTER TABLE products RENAME COLUMN nm TO name;

SELECT name, price FROM products;

-- The view and the index now use the new name too
SELECT * FROM cheap;
SELECT name AS indexed FROM pragma_index_info('idx_products_nm');

The table reads back with a name column. The view still works and its column header is now name too, and pragma_index_info shows the index covers name. Code outside the database that still says nm is another matter: rename it there in the same release. SQL Server has no RENAME COLUMN; it uses the sp_rename procedure (see section 7). To rename the table itself, use ALTER TABLE products RENAME TO items.

4Drop a column: DROP COLUMN

ALTER TABLE … DROP COLUMN removes a column and its data. SQLite has supported it since 3.35 (March 2021), with a few limits: it refuses a column that is part of an index until you drop the index, and it never drops a PRIMARY KEY or UNIQUE column. The last statement below hits that second rule.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    legacy_code TEXT,
    fax TEXT
);
INSERT INTO users (name, email, legacy_code, fax)
VALUES ('Ana', '[email protected]', 'A1', '555-0101');
CREATE INDEX idx_users_legacy ON users (legacy_code);

-- Drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN fax;

-- An indexed column: drop the index first
DROP INDEX idx_users_legacy;
ALTER TABLE users DROP COLUMN legacy_code;

SELECT * FROM users;

-- A UNIQUE or PRIMARY KEY column can't be dropped
ALTER TABLE users DROP COLUMN email;

After the two drops, SELECT * shows only id, name and email. Dropping email fails with cannot drop UNIQUE column: "email". Had the index still been there, dropping legacy_code would have failed with error in index idx_users_legacy after drop column: no such column: legacy_code. To drop a UNIQUE or key column in SQLite, rebuild the table without it (section 6).

5Make a column nullable, or NOT NULL

PostgreSQL spells this ALTER COLUMN … DROP NOT NULL and ALTER COLUMN … SET NOT NULL, and recent SQLite accepts the same two statements (this page runs 3.53.4; older SQLite has no ALTER COLUMN at all and needs the rebuild in section 6). SET NOT NULL checks the existing rows, so fill in any NULL values first. The final insert shows the new rule being enforced.

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    phone TEXT
);
INSERT INTO contacts (name, phone) VALUES ('Ana', '555-0101'), ('Ben', NULL);

-- Make name nullable
ALTER TABLE contacts ALTER COLUMN name DROP NOT NULL;
INSERT INTO contacts (name, phone) VALUES (NULL, '555-0199');

-- Make phone required: fill in the NULLs first
UPDATE contacts SET phone = 'none' WHERE phone IS NULL;
ALTER TABLE contacts ALTER COLUMN phone SET NOT NULL;

SELECT id, name, phone FROM contacts;

-- From now on a missing phone is rejected
INSERT INTO contacts (name) VALUES ('Cleo');

Row 3 has a NULL name, which the original table would have rejected. Ben's missing phone became none, so SET NOT NULL went through, and Cleo's insert then fails with NOT NULL constraint failed: contacts.phone. Without that UPDATE, SET NOT NULL itself fails with a plain constraint failed. SQLite still has no ALTER COLUMN … TYPE or ALTER COLUMN … SET DEFAULT; both are syntax errors here. MySQL and SQL Server change nullability by restating the column's type, as the table in section 7 shows.

6Change a column type in SQLite: rebuild the table

For anything ALTER TABLE can't do in SQLite (a new type, a new default, adding or removing a constraint), build the table you want under a temporary name, copy the rows across, drop the old table and rename the new one into its place. Inside one transaction, nobody sees the table half-built. The copy is an ordinary INSERT … SELECT, so it can convert values on the way. Here price goes from TEXT NOT NULL to a nullable REAL.

CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price TEXT NOT NULL
);
INSERT INTO items (name, price) VALUES ('Pen', '2.50'), ('Mug', '9');

-- Goal: price becomes a nullable REAL. Rebuild the table:
PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE items_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL
);
INSERT INTO items_new (id, name, price)
SELECT id, name, CAST(price AS REAL) FROM items;
DROP TABLE items;
ALTER TABLE items_new RENAME TO items;
COMMIT;
PRAGMA foreign_keys = ON;

INSERT INTO items (name, price) VALUES ('Cap', NULL);
SELECT id, name, price, typeof(price) AS type FROM items;

The text prices '2.50' and '9' come back as the real numbers 2.5 and 9.0, and the new row accepts a NULL price. Copying id keeps the keys, so rows in other tables still point at the right items. Foreign keys are switched off around the transaction (the pragma does nothing inside one) so that dropping the old table doesn't touch rows that reference it. Indexes and triggers go with the old table, so create them again afterwards. Drop any view that reads the table before you start: otherwise the rename fails with error in view v: no such table: main.items. Recreate the view at the end.

7Other databases: SQL Server, MySQL, PostgreSQL

Adding a column is close to standard everywhere, but renaming and changing a column are not. Each form below was run on PostgreSQL 18, MySQL 8.4 and SQL Server's T-SQL (through Babelfish), except where the note says otherwise.