【MySQL】DEFAULT NULLと外部キー制約の同居

同居できるんかなと（ほんとはMariaDB）

mysql> CREATE TABLE users (
   id SERIAL,
   name VARCHAR(255)
 );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE posts (
   user_id BIGINT(20) UNSIGNED DEFAULT NULL,  # ← あえてつける
   message VARCHAR(255),
   FOREIGN KEY (user_id) REFERENCES users(id)
 );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO posts VALUE (3, "Hello, outside child");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foo`.`posts`, CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))
# ← わかる

mysql> INSERT INTO posts VALUE (NULL, "Hello, lost child");
Query OK, 1 row affected (0.00 sec)
# ← ファ！？

mysql> SELECT * FROM posts;
+---------+-------------------+
| user_id | message           |
+---------+-------------------+
|    NULL | Hello, lost child |
+---------+-------------------+
1 row in set (0.00 sec)

まじかーできるのかー

NOT NULL つけよ？

mysql> CREATE TABLE posts (
   user_id BIGINT(20) UNSIGNED NOT NULL,
   message VARCHAR(255),
   FOREIGN KEY (user_id) REFERENCES users(id)
 );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO posts VALUE (NULL, "Hello, lost child");
ERROR 1048 (23000): Column 'user_id' cannot be null
# ← それがほしかった

Don't Repeat Yourself