refirio.org

Memo

■外部キー制約

親テーブルの更新/削除の時の挙動(ON DELETE句,ON UPDATE句) - テーブルの作成 - MySQLの使い方
https://www.dbonline.jp/mysql/table/index12.html

MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita
https://qiita.com/suin/items/21fe6c5a78c1505b19cb

外部キー制約を使うと、テーブル間のデータ整合性を厳密に保つことができる
制約は「FOREIGN KEY(外部キー名) REFERENCES 親テーブル名(カラム名) ON オプション」のように指定でき、オプションには以下のようなものがある

RESTRICT:
親テーブルに対して削除または更新を行うとエラーとなる
設定を省略した場合は RESTRICT を設定したのと同じ

NO ACTION:
親テーブルに対して削除または更新を行うとエラーとなる
RESTRICT を設定した場合と同じ

CASCADE:
親テーブルに対して削除または更新を行うと、子テーブルで同じ値を持つカラムのデータに対して削除または更新を行う

上記のように、MySQLでは「NO ACTION」と「RESTRICT」は同じ挙動となる
以下、実際にテーブルへの関連データを登録して、外部キー制約（FOREIGN KEY 制約）などの挙動を試してみる

CREATE TABLE goods(
    id INT,
    name VARCHAR(10),
    INDEX(name)
) ENGINE=InnoDB;
INSERT INTO goods VALUES(1, '消しゴム');
INSERT INTO goods VALUES(2, '鉛筆');
INSERT INTO goods VALUES(3, 'ボールペン');


RESTRICT
CREATE TABLE sales1(
    id INT,
    name VARCHAR(10),
    date DATE,
    INDEX(name),
    FOREIGN KEY(name) REFERENCES goods(name) ON UPDATE RESTRICT
) ENGINE=InnoDB;
INSERT INTO sales1 VALUES(1, '鉛筆', '2017-01-20');
INSERT INTO sales1 VALUES(2, '消しゴム', '2017-02-05');

UPDATE goods SET name = 'ケシゴム' WHERE id = 1; #1451 - Cannot delete or update a parent row: a foreign key constraint fails


NO ACTION
CREATE TABLE sales2(
    id INT,
    name VARCHAR(10),
    date DATE,
    INDEX(name),
    FOREIGN KEY(name) REFERENCES goods(name) ON DELETE NO ACTION
) engine=InnoDB;
INSERT INTO sales2 VALUES(1, '鉛筆', '2017-01-20');
INSERT INTO sales2 VALUES(2, '消しゴム', '2017-02-05');

DELETE FROM goods WHERE id = 2; #1451 - Cannot delete or update a parent row: a foreign key constraint fails


CASCADE（MySQLにおいては、「NO ACTION」と「RESTRICT」は同じ挙動）
CREATE TABLE sales3(
    id INT,
    name VARCHAR(10),
    date DATE,
    INDEX(name),
    FOREIGN KEY(name) REFERENCES goods(name) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT INTO sales3 VALUES(1, '鉛筆', '2017-01-20');
INSERT INTO sales3 VALUES(2, '消しゴム', '2017-02-05');
INSERT INTO sales3 VALUES(3, '消しゴム', '2017-03-02');
INSERT INTO sales3 VALUES(4, '鉛筆', '2017-04-26');

UPDATE goods SET name = 'ケシゴム' WHERE id = 1; # sales3 テーブルの name も更新される
DELETE FROM goods WHERE id = 1; # sales3 テーブルの name が等しいデータも削除される


上記ではテーブル作成時に制約を追加している
後から追加する場合、以下のようにALTER TABLEで追加する
制約名は必須では無いようだが、削除のときに使用するので付けておくのが無難か
以下の制約名はLaravelが自動作成した制約名。制約名はランダムな文字列を付けることが多い？
ALTER TABLE sales1 ADD CONSTRAINT FK_23A0E6661220EA6 FOREIGN KEY(name) REFERENCES goods(name)


以下のようにすれば削除できる
ALTER TABLE sales1 DROP CONSTRAINT FK_23A0E6661220EA6;
Memo

Advertisement
