The Effect of ALTER TABLE on Null and Default Value Attributes

8.4.1 Problem

You changed a column definition, but MySQL modified the column's NULL value and default value attributes when you didn't tell it to.

8.4.2 Solution

Those attributes are part of the column definition. If you don't specify them explicitly, MySQL chooses their values for you. So just be more specific about how you want the column defined.

8.4.3 Discussion

When you MODIFY or CHANGE a column, you can also specify whether or not the column can contain NULL values, and what its default value is. In fact, if you don't do this, MySQL automatically assigns values for these attributes, with the result that the column may end up defined not quite the way you intend. To see this, try the following sequence of commands. First, modify j so that it cannot contain NULL values and to have a default value of 100, then see what SHOW COLUMNS tells you:[2]

[2] The LIKE 'str' clause for SHOW COLUMNS causes the statement to show information only for columns having names that match the string. The string can contain SQL pattern characters if you want it to match several column names. See Recipe 9.6.

mysql> ALTER TABLE mytbl MODIFY j INT NOT NULL DEFAULT 100;
mysql> SHOW COLUMNS FROM mytbl LIKE 'j';
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| j | int(11) | | | 100 | |
+-------+---------+------+-----+---------+-------+

So far, so good. Now if you were to decide to change j from INT to BIGINT, you might try the following statement:

mysql> ALTER TABLE mytbl MODIFY j BIGINT;

However, that also undoes the NULL and DEFAULT specifications of the previous ALTER TABLE statement:

mysql> SHOW COLUMNS FROM mytbl LIKE 'j';
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| j | bigint(20) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+

To avoid this, the MODIFY statement should specify the null and default value attributes explicitly:

mysql> ALTER TABLE mytbl MODIFY j BIGINT NOT NULL DEFAULT 100;
mysql> SHOW COLUMNS FROM mytbl LIKE 'j';
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| j | bigint(20) | | | 100 | |
+-------+------------+------+-----+---------+-------+

The implication of this exercise is that if a column is defined such that its null and default value attributes are not what MySQL would assign automatically, you must specify them explicitly in your ALTER TABLE statement if you don't want them to change when you modify some other aspect of the column definition.

This fact is important for some of the recipes shown in Chapter 9, such as one program that converts a table to use VARCHAR rather than CHAR columns, and another that adds new elements to ENUM or SET columns. In each case, the programs take care to avoid unintended column changes by including NULL and DEFAULT specifiers in the ALTER TABLE statements that they generate.