【MySQL】カラム定義を変更する際の注意点

はじめに

ALTER TABLE MODIFY文でカラム定義を変更した際に、元の定義が削除されてしまったので、その原因と対処法について調べた内容まとめます。

結論

• ALTER TABLE MODIFY(またはCHANGE)でカラム定義を変更すると、新しい定義で上書きされる
• 元からあった定義は引き継がれない

CHANGE または MODIFY を使用してカラム定義を変更する場合、PRIMARY KEY や UNIQUE などのインデックス属性以外の、新しいカラムに適用するデータ型およびすべての属性を定義に含める必要があります。 元の定義には存在するが、新しい定義として指定されていない属性は引き継がれません。

例

• 変更前：employeesテーブルのdepartment_nameカラムに下記の定義がある状態
  • varchar(10)
  • 非NULL制約 あり
  • default値は"temp"

mysql> DESC employees;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| department_name | varchar(10)  | NO   |     | temp    |                |

• varchar(5)に変更するMODIFY文を実行

mysql> ALTER TABLE employees modify COLUMN department_name varchar(5);

• 変更後
  • varchar(5)
  • 非NULL制約 なし 　# 元の定義が削除された状態
  • default値 なし　# 元の定義が削除された状態

mysql> DESC employees;
 +-----------------+--------------+------+-----+---------+----------------+
 | Field           | Type         | Null | Key | Default | Extra          |
 +-----------------+--------------+------+-----+---------+------------
-| department_name | varchar(10)  | NO   |     | temp    |                |
+| department_name | varchar(5)   | YES  |     | NULL    |                |

対処法

• ALTER TABLE MODIFYでカラム定義を変更する際は、元の定義も含めて実行する
• ALTER TABLE MODIFYで設定できない定義は、ALTER TABLE MODIFY実行後に再設定する

例

• varchar(5)に変更するMODIFY文に元からの定義である NOT NULL を含めて実行する

- mysql> ALTER TABLE employees modify COLUMN department_name varchar(5);
+ mysql> ALTER TABLE employees modify COLUMN department_name varchar(5) NOT NULL;

• ALTER TABLE MODIFY文で設定しなかったdefault設定は削除される

mysql> DESC employees;
 +-----------------+--------------+------+-----+---------+----------------+
 | Field           | Type         | Null | Key | Default | Extra          |
 +-----------------+--------------+------+-----+---------+----------------+
-| department_name | varchar(10)  | NO   |     | temp    |                |
+| department_name | varchar(5)   | NO   |     | NULL    |                |

• default設定を再設定する

mysql> ALTER TABLE employees ALTER department_name SET DEFAULT "temp";

mysql> DESC employees;
 +-----------------+--------------+------+-----+---------+----------------+
 | Field           | Type         | Null | Key | Default | Extra          |
 +-----------------+--------------+------+-----+---------+----------------+
-| department_name | varchar(5)   | NO   |     | NULL    |                |
+| department_name | varchar(5)   | NO   |     | temp    |                |

追記

• 非NULL制約を設定する

ALTER TABLE テーブル名 MODIFY COLUMN カラム名 データ型名 NOT NULL;

• 非NULL制約を解除する

ALTER TABLE テーブル名 MODIFY COLUMN カラム名 データ型名;