MySQL の 外部キー制約と DEFAULT NULL

Users

mysql> CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc users;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Articles

user_id に DEFAULT NULL つける

CREATE TABLE `articles` (
  `user_id` int(10) UNSIGNED DEFAULT NULL, 
  `title` varchar(255),
  `body` text,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  INDEX `index_articles_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc articles;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| user_id | int(10) unsigned | YES  | MUL | NULL    |       |
| title   | varchar(255)     | YES  |     | NULL    |       |
| body    | text             | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

普通に順番にINSERT してみる

mysql> INSERT INTO users (name) VALUES ("7kaji");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO articles (user_id, title, body) VALUES (1, "hoge", "hogehoge");
Query OK, 1 row affected (0.00 sec)

消そうとする

mysql> DELETE FROM users WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`blog_development`.`articles`, CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))

ふむふむ。よい。

いっかい消す

mysql> DELETE FROM articles;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM users;
Query OK, 1 row affected (0.00 sec)

user がいない状態で、もっかいやる

mysql> INSERT INTO articles (user_id, title, body) VALUES (1, "hoge", "hogehoge");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`blog_development`.`articles`, CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))

ふむ。

user_id に NULL 指定してみる

mysql> INSERT INTO articles (user_id, title, body) VALUES (NULL, "hoge", "hogehoge");
Query OK, 1 row affected (0.00 sec)

あ、INSERT できちゃった

mysql> SELECT * FROM articles;
+---------+-------+----------+
| user_id | title | body     |
+---------+-------+----------+
|    NULL | hoge  | hogehoge |
+---------+-------+----------+
1 row in set (0.00 sec)

NOT NULL つける

CREATE TABLE `articles` (
  `user_id` int(10) UNSIGNED NOT NULL, 
  `title` varchar(255),
  `body` text,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  INDEX `index_articles_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

もっかい

mysql> INSERT INTO articles (user_id, title, body) VALUES (NULL, "hoge", "hogehoge");
ERROR 1048 (23000): Column 'user_id' cannot be null

まとめ

外部キー制約と DEFAULT NULL はいっしょに設定できてしまうので、NOT NULL つけようぬ