MySQLにおけるNOT NULL制約とDEFAULT制約

Tech

こんにちは、小林です。
今回は、MySQLのNOT NULL制約とDEFAULT制約について話をしたいと思います。

私はNOT NULL制約に関して、勘違いをして覚えてしまっており、レビュー時にそれを元に指摘をしてしまうことがあります。

それがついに3回目になってしまったので、ブログとしてまとめ、自分の中に定着させようと思い、筆を取りました。

勘違いしていたこと

「MySQLにおいて、NULLが格納されている既存のカラムに新しくNOT NULL制約を付与してもNOT NULL制約違反にはならない」という勘違いをしていました。

思考としては、以下のような感じです。

  1. 更新用のDML(INSERT、UPDATE、DELETE)を実行したときに制約は適用される
  2. 制約の追加(ALTER)はDDLのため、制約は適用されない
    ※但し、制約の追加後の最初の登録、更新時に該当カラムにNULL以外の値を入れないといけない

勘違いをしてしまっていた理由は、今回二つあることがわかりました。

制約が適用されるタイミングを誤認していた

まず、制約の適用されるタイミングを誤認していました。

過去のプロジェクトで、DMLの実行時にトリガーを発火させるということをしていたことがあり、「トリガーはDMLの実行時に発火する」と覚えてしまっていました。
※トリガーはDDL実行時にも発火させることが可能です。

そこから、いつの間にかトリガーと制約を取り違えてしまい、「制約がチェックされるのはDMLの実行時のみ」と思い込んでいました。

これにより、DDLの実行時には影響がないと勘違いをしていました。

MySQLとPostgreSQLにてNOT NULL制約の扱いが異なっていた

こちらの方が本題で詳細は後述しますが、以下によって勘違いをしていました。

  1. PostgreSQLでは、DEFAULT制約なしのNOT NULL制約を付与するとエラーとなる
  2. MySQLでは、DEFAULT制約なしのNOT NULL制約を付与してもエラーとならない
    ※条件によります
  3. MySQLではエラーにならないのだから、制約は動いていない

実際のところ、どうなのか？

検証

検証に使用したSQLはこちらのリポジトリに用意してあります。

検証手順は、以下の通りです。

  1. テーブルを作成する
  2. テーブルにレコードを登録する
  3. 新しいカラムを追加する
    1. カラムの追加と同時にNOT NULL制約を付与する・・・パターン1
    2. カラムを追加後にNOT NULL制約を付与する・・・パターン2

MySQL

パターン1

使用したSQLは、こちら。

mysql> SELECT * FROM not_null_when_add_columns1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | nobunaga  |
|  2 | hideyoshi |
+----+-----------+
2 rows in set (0.00 sec)

mysql>
mysql> ALTER TABLE not_null_when_add_columns1 ADD COLUMN added_column VARCHAR(255) NOT NULL;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM not_null_when_add_columns1;
+----+-----------+--------------+
| id | name      | added_column |
+----+-----------+--------------+
|  1 | nobunaga  |              |
|  2 | hideyoshi |              |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql>

カラムの追加に成功しました。※NUllが格納されている場合は、NULLと表示されます。

パターン2

使用したSQLは、こちら。

mysql> SELECT * FROM not_null_when_add_columns2;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | nobunaga  |
|  2 | hideyoshi |
+----+-----------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE not_null_when_add_columns2 ADD COLUMN added_column VARCHAR(255);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM not_null_when_add_columns2;
+----+-----------+--------------+
| id | name      | added_column |
+----+-----------+--------------+
|  1 | nobunaga  | NULL         |
|  2 | hideyoshi | NULL         |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE not_null_when_add_columns2 MODIFY added_column VARCHAR(255) NOT NULL;
ERROR 1138 (22004): Invalid use of NULL value
mysql>

カラムを追加した際に、カラムにNULLが設定されています。とエラーが出力されました。

PostgreSQL

パターン1

使用したSQLは、こちら。

(結果一部抜粋)
play_with_sql=# SELECT * FROM not_null_when_add_columns1;
id | name
----+-----------
1 | nobunaga
2 | hideyoshi
(2 rows)

play_with_sql=#
play_with_sql=# ALTER TABLE not_null_when_add_columns1 ADD COLUMN added_column VARCHAR(32) NOT NULL;
ERROR: column "added_column" of relation "not_null_when_add_columns1" contains null values
play_with_sql=#

カラムを追加した際に、カラムにNULLが設定されています。とエラーが出力されました。

パターン2

使用したSQLは、こちら。

(結果一部抜粋)
play_with_sql=# SELECT * FROM not_null_when_add_columns2;
 id |   name
----+-----------
  1 | nobunaga
  2 | hideyoshi
(2 rows)

play_with_sql=#
play_with_sql=# ALTER TABLE not_null_when_add_columns2 ADD COLUMN added_column VARCHAR(32);
ALTER TABLE
play_with_sql=# SELECT * FROM not_null_when_add_columns2;
 id |   name    | added_column
----+-----------+--------------
  1 | nobunaga  | (null)
  2 | hideyoshi | (null)
(2 rows)

play_with_sql=#
play_with_sql=# ALTER TABLE not_null_when_add_columns2 ALTER COLUMN added_column SET NOT NULL;
ERROR:  column "added_column" of relation "not_null_when_add_columns2" contains null values
play_with_sql=#

カラム追加時にはエラーになりませんでしたが、NOT NULL制約を付与した際にエラーが出力されました。

結論

MySQL

  • NOT NULL制約のあるカラムを追加する際に、DEFAULT制約が設定されていない場合、(SQLモードによるが)その型のDEFAULT値が設定されるため、NOT NULL制約を回避する
  • しかし、カラムを追加後にNOT NULL制約を追加する場合には先にNULLが設定され、そのカラムに対してNOT NULL制約を設定されることとなり、エラーとなる

明示的な DEFAULT 句のない NOT NULL カラムに対するデータエントリでは、INSERT または REPLACE ステートメントにカラムの値を含まれていない場合、または UPDATE ステートメントがカラムを NULL に設定する場合、MySQL はその時点で有効な SQL モードに従ってカラムを処理します。

参考：https://dev.mysql.com/doc/refman/8.0/ja/data-type-defaults.html

PostgreSQL

  • DEFAULT制約が指定されていないとNULLが設定されるため、エラーとなる

新しい列にはデフォルト値が初期値として入ります（DEFAULT句を指定しない場合はNULL値が入ります）。

参考：https://www.postgresql.jp/document/14/html/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN

さいごに

毎回、調査して間違っていることに気づくのですが、しばらく経つと忘れてしまい、また指摘しまうという悪循環に陥っていました。
しかし、それもこれで終わりです。おそらく。。。きっと。。。多分。。。

タイトルとURLをコピーしました