SQL · Theory

ALTER Table

Theory

14/439

ALTER Table

ALTER TABLE changes a table that already exists — add a column, rename, change type — without dropping all data. ALTER TABLE students ADD COLUMN email VARCHAR(80); adds email. Existing rows get NULL in that column unless you set a DEFAULT.

ADD / DROP COLUMN, RENAME COLUMN, MODIFY type, ADD INDEX, ADD FOREIGN KEY — all are ALTER. MySQL 8 uses RENAME COLUMN old TO new. Older 5.7 often used CHANGE.

Trap: MODIFY VARCHAR(10) when names are already 30 characters — those rows can fail or get cut. Trap: DROP COLUMN on a column the app still writes to.

On the example next to this theory — ALTER TABLE changes structure without rebuilding the whole table.

Exam tip

ALTER TABLE students ADD COLUMN email VARCHAR(80);

ALTER Table — sample query

CREATE TABLE items (id INTEGER PRIMARY KEY, title TEXT);
ALTER TABLE items ADD COLUMN price REAL DEFAULT 0;
INSERT INTO items (id, title, price) VALUES (1, 'Pen', 10);
SELECT * FROM items;

ALTER TABLE changes structure without rebuilding the whole table.

Short notes

DefALTER TABLE changes structure of an existing table.
RuleADD COLUMN is the common student task. Existing rows get NULL / DEFAULT.
TrapShrinking a VARCHAR so old data no longer fits.

SQL · Theory

ALTER Table

← All stacks

Theory

14/439

ALTER Table

ADD / DROP COLUMN, RENAME COLUMN, MODIFY type, ADD INDEX, ADD FOREIGN KEY — all are ALTER. MySQL 8 uses RENAME COLUMN old TO new. Older 5.7 often used CHANGE.

Trap: MODIFY VARCHAR(10) when names are already 30 characters — those rows can fail or get cut. Trap: DROP COLUMN on a column the app still writes to.

On the example next to this theory — ALTER TABLE changes structure without rebuilding the whole table.

Exam tip

ALTER TABLE students ADD COLUMN email VARCHAR(80);

ALTER Table — sample query

CREATE TABLE items (id INTEGER PRIMARY KEY, title TEXT);
ALTER TABLE items ADD COLUMN price REAL DEFAULT 0;
INSERT INTO items (id, title, price) VALUES (1, 'Pen', 10);
SELECT * FROM items;

ALTER TABLE changes structure without rebuilding the whole table.

Short notes

DefALTER TABLE changes structure of an existing table.
RuleADD COLUMN is the common student task. Existing rows get NULL / DEFAULT.
TrapShrinking a VARCHAR so old data no longer fits.

ALTER Table

ALTER Table — sample query

Short notes

Questions

ALTER Table

ALTER Table — sample query

Short notes

Questions