Created
September 25, 2018 08:16
-
-
Save S2/7a0470d6bc7b3235dfec1e8a3bbc3a53 to your computer and use it in GitHub Desktop.
正規化されていない変な外部キーの貼り方をしていたら理不尽な消え方をしたのでまとめた。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DROP TABLE hoge_fuga_piyo; | |
| DROP TABLE hoge_fuga; | |
| DROP TABLE hoge; | |
| -- ----------------------------------------------------- | |
| -- Table `hoge` | |
| -- ----------------------------------------------------- | |
| CREATE TABLE IF NOT EXISTS `hoge` ( | |
| id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL , | |
| name varchar(255) NOT NULL | |
| ) | |
| ENGINE = InnoDB | |
| DEFAULT CHARACTER SET = utf8; | |
| -- ----------------------------------------------------- | |
| -- Table `hoge_fuga` | |
| -- ----------------------------------------------------- | |
| CREATE TABLE IF NOT EXISTS `hoge_fuga` ( | |
| id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL , | |
| hoge_id INT(10) UNSIGNED NOT NULL , | |
| name varchar(255) NOT NULL , | |
| CONSTRAINT `fk_hoge_fuga1` | |
| FOREIGN KEY (`hoge_id` ) | |
| REFERENCES `hoge` (`id` ) | |
| ON DELETE CASCADE | |
| ON UPDATE CASCADE | |
| ) | |
| ENGINE = InnoDB | |
| DEFAULT CHARACTER SET = utf8; | |
| -- ----------------------------------------------------- | |
| -- Table `hoge_fuga_piyo` | |
| -- ----------------------------------------------------- | |
| CREATE TABLE IF NOT EXISTS `hoge_fuga_piyo` ( | |
| hoge_id INT(10) UNSIGNED NOT NULL , --- そもそもhoge_fuga_idから引けるから正規化できてない。 | |
| hoge_fuga_id INT(10) UNSIGNED NOT NULL , | |
| name varchar(255) NOT NULL , | |
| CONSTRAINT `fk_hoge_fuga_piyo1` | |
| FOREIGN KEY (`hoge_id` ) | |
| REFERENCES `hoge_fuga` (`hoge_id` ) | |
| ON DELETE CASCADE | |
| ON UPDATE CASCADE , | |
| CONSTRAINT `fk_hoge_fuga_piyo2` | |
| FOREIGN KEY (`hoge_fuga_id` ) | |
| REFERENCES `hoge_fuga` (`id` ) | |
| ON DELETE CASCADE | |
| ON UPDATE CASCADE | |
| ) | |
| ENGINE = InnoDB | |
| DEFAULT CHARACTER SET = utf8; | |
| INSERT INTO hoge(id,name)VALUES(1,"hoge"); | |
| INSERT INTO hoge_fuga (id,hoge_id,name) VALUES(1,1,"fuga"); | |
| INSERT INTO hoge_fuga (id,hoge_id,name) VALUES(2,1,"fuga2"); | |
| INSERT INTO hoge_fuga_piyo (hoge_id,hoge_fuga_id,name)VALUES(1,1,"hoge_fuga_piyo"); | |
| --- id = 2 、つまり、hoge_fuga_piyoの列には該当しないものを削除する | |
| DELETE from hoge_fuga WHERE id = 2; | |
| --- でもhoge_fuga_piyoの列が消える。 | |
| --- 多分hoge_fugaを消した時にそこに参照があるテーブルの列を削除している。DELETE FROM referenced_table WHERE id = referenced_idみたいな感じ。 | |
| SELECT * FROM hoge_fuga_piyo; --- 0 rows |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment