ON DELETE、ON UPDATE をそれぞれCASCADEに設定した時の挙動

Question

MySQLで親テーブルの更新/削除の時の挙動で、
ON DELETE
ON UPDATE をそれぞれCASCADEに設定しました。

親テーブルを削除すると、紐付いている子テーブルも更新削除されるようになったのですが、子テーブルをUPDATEをしようとするとエラーになってしまいました。

今、手元にエラー文が無いのですが、ON DELETE、ON UPDATE をCASCADEにしたことが原因のようです。
これを回避するにはどうすればいいのでしょうか？

親テーブル
id
name

子テーブル
id
parent_id(親テーブルid) NULL有り
name

Answer 1

まずは

create table parent(pid int primary key,pname varchar(20));
create table child(cid int primary key,cname varchar(20),pid int,foreign key(pid) references parent(pid) on delete cascade on update cascade);
insert into parent values(1,'p1'),(2,'p2'),(3,'p3'),(4,'p4');
insert into child values(1,'c1-1',1),(2,'c1-2',1),(3,'c1-3',1),(4,'c2-1',2),(5,'c2-2',2),(6,'c3-1',3),(7,'c3-2',3);

その上で

delete from parent where pid=2;//childの4,5が削除
update parent set pid=5 where pid=3;//childの6,7のpidが5に変わる

ここまではOKですか？
ここで

update child set pid=4 where cid=6;//cid=6のpid=5を4に変える

parentにpid=4が存在するのでOK

update child set pid=6 where cid=7;//cid=7のpid=5を6に変える

parentに存在しないpidに変更しようとしても誓約にひっかかります

Comments

[退会済みユーザー]

ありがとうございました！
よく確認したところ、親IDが存在しなくなっていたのが原因でした。

Answer 2

子テーブルをUPDATEをしようとするとエラーになってしまいました。

エラーになって当然です。子テーブルから連動して親テーブルまで書き換えるような機能はありません。

…というより、ON DELETE CASCADEはともかく、ON UPDATE CASCADEを恒常的に使わないといけない（関連付けられるキー自体の書き換えが相次ぐ）データベース設計自体に問題があると考えます。

Comments

[退会済みユーザー]

親テーブルはそのままで、子テーブル側のみの値を変えたかったのです。
ON DELETE CASCADE、ON UPDATE CASCADEは親テーブルが変更された時だけ、子テーブルを更新するものではなかったんですね。
ありがとうございます。

[maisumakun]

子テーブル側だけ値を変えたいにしても、（ON UPDATE CASCADEの前提となる）外部キーがかかっている以上は、親テーブルにあるものしか入れられません。

Answer 3

現象を再現できるUPDATE、使っているテーブルのCREATE, データを何件かINSERT文、その時表示されたエラーメッセージを質問に追記しては？

たぶん、親テーブルに存在しないキーで子テーブルをUPDATEして、親テーブルにデータがない、って怒られたのでは？

親テーブルの更新/削除の時の挙動

ON DELETE句やON UPDATE句にCASCADEを指定した場合は、親テーブルに対して更新を行うと子テーブルで同じ値を持つカラムの値も合わせて更新されます。また親テーブルに対して削除を行うと子テーブルで同じ値を持つデータが削除されます。

わからないからといって掲示板に質問する前に、使い方くらいググって調べたり、CASCADEを付つけない時の挙動を確認しては？

Comments

[退会済みユーザー]

コメントありがとうございます。
再現できるものができましたら修正させていたます。
親テーブルの更新削除は特に問題ないです。親テーブルを更新削除すると、子テーブルも更新削除されます。
今回問題なのは、子テーブル側を更新しようとした時の挙動です。

[Orlofsky]

変更しようとした子テーブルのキーが親テーブルに存在しないのでエラーになったのでしょう。