Databases & SQL · Class 12 · Inserting & Changing Data

MySQLDDL⏱️ 10 min read

Altering a Table

You designed the table in September and in January the school wants to record house colours too. You do not have to start again. alter table changes the structure of a table that is already full of data — which puts it back in DDL, alongside create and drop.

1The four jobs it does

alter table, in the syllabus

Job	Written as
Add a column	alter table t add <col> <type>;
Change a column's data type	alter table t modify <col> <new type>;
Remove a column	alter table t drop <col>;
Add or remove the primary key	alter table t add primary key (<col>); / drop primary key;

2Adding a column

MySQL command line client

mysql> alter table students add email varchar(40);

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc students;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| grade | int | YES | | NULL | |

| dob | date | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

The new column goes on the end, and the table's degree has gone from 6 to 7. Every row that already existed now has an email of NULL, because nobody has supplied one — there is no other sensible thing to put there.

Unless you give a default, in which case every existing row gets it:

MySQL command line client

mysql> alter table students add house varchar(10) default "Blue";

Query OK, 0 rows affected (0.16 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from students;

+------+-------+-------+---------+------------+---------+-------+-------+

+------+-------+-------+---------+------------+---------+-------+-------+

| 1099 | Veena | 10 | 90.5 | 2010-11-20 | Singtam | NULL | Blue |

| 2035 | Ravi | 12 | 83.75 | 2008-08-13 | Singtam | NULL | Blue |

+------+-------+-------+---------+------------+---------+-------+-------+

3 rows in set (0.00 sec)

Two new columns, side by side, making the point: email is NULL everywhere and house is Blue everywhere. The difference is the default.

3Changing a column's data type

Forty characters turns out to be tight for an email address:

MySQL command line client

mysql> alter table students modify email varchar(60);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc students;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| grade | int | YES | | NULL | |

| dob | date | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

8 rows in set (0.00 sec)

Widening is safe; narrowing is not

Going from varchar(40) to varchar(60) cannot lose anything. Going the other way, or from varchar to int, has to do something with values that no longer fit — and what it does depends on the server's settings. Change a type downwards only on a table you have backed up.

4Removing a column

MySQL command line client

mysql> alter table students drop email;

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc students;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| grade | int | YES | | NULL | |

| dob | date | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

The column is gone from every row at once, and so is everything that was stored in it. There is no undo, exactly as with drop table.

alter … drop vs delete

alter table students drop city; removes a column — the city of every student, for ever. delete from students where …; removes rows. One cuts a stripe down the table, the other cuts one across it.

5Adding and removing the primary key

A table built without a primary key can be given one afterwards. Note the column name goes in brackets:

MySQL command line client

mysql> create table temp1 (a int, b varchar(5));

Query OK, 0 rows affected (0.11 sec)

mysql> alter table temp1 add primary key (a);

Query OK, 0 rows affected (0.47 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc temp1;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| a | int | NO | PRI | NULL | |

| b | varchar(5) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Column a now shows PRI, and its Null has flipped to NO on its own — because a primary key can never be NULL.

Removing it needs no column name, since a table has only one primary key:

MySQL command line client

mysql> alter table temp1 drop primary key;

Query OK, 0 rows affected (0.50 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc temp1;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| a | int | NO | | NULL | |

| b | varchar(5) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Notice what did not come back

Key is empty again, but Null is still NO. Dropping the primary key removed the uniqueness, not the not-null rule that came with it. To allow NULLs again you would have to modify the column as well. This is the sort of detail that only shows up if you actually run the commands and read the output.

Adding a primary key can also fail: if the column already holds a duplicate value or a NULL, MySQL refuses, because the rule cannot be true of the data that is already there.

6Recap

add col type

New column on the end; existing rows get NULL, or the default.

modify col type

Changes the data type. Widening is safe.

drop col

Removes the column from every row. No undo.

add primary key (col) / drop primary key

Brackets to add, no column name to remove.

Quick Check

A table already holds 40 rows. You run alter table students add house varchar(10); What is in house for those rows?

Quick Check

Which command removes the city column from a table?

Quick Check

Which is correct for making column a the primary key of an existing table?

Quick Check

alter table belongs to which group?

← Previous

❌ Deleting Rows

Selecting Columns 📄