[解決済み】外部キー制約。ON UPDATEとON DELETEを使用する場合

質問

MySQL Workbenchを使用してデータベーススキーマを設計していますが、図を書いてそれを変換できるのでとてもクールです :P

とにかく、InnoDBを使うことにしたのは、外部キーがサポートされているからです。しかし、1つ気づいたのは、外部キーにOn UpdateとOn Deleteオプションを設定できることだ。誰か、簡単な例で "Restrict", "Cascade" と set null がどこで使われるのか説明してくれますか？

例えば user テーブルがあり、その中に userID . そして、メッセージテーブルがあるとします message これは多対多で、2つの外部キー (同じ主キーを参照する) を持っています。 userID での user テーブル)を使用することができます。この場合、On UpdateとOn Deleteのオプションの設定は何か役に立つのでしょうか？もしそうなら、どちらを選べばいいのでしょうか？もしこれが良い例でないなら、これらがどのように有用であるかを説明する良い例を考えていただけませんか？

ありがとうございます。

解決方法は？

データベースに制約をかけることを躊躇しないでください。データベースの一貫性を保つことができ、それがデータベースを使用する良い理由の1つです。特に、複数のアプリケーションから要求がある場合（または、1つのアプリケーションで、異なるソースを使用するダイレクトモードとバッチモードがある場合）には、なおさらです。

MySQLでは、postgreSQLのような高度な制約はありませんが、少なくとも外部キー制約はかなり高度なものです。

例えば、ある会社のテーブルと、その会社の人を含むユーザー・テーブルを例にとります。

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

を見てみましょう。 オンアップデート 節があります。

• 更新時の制限 : デフォルトの もし、COMPANYテーブルのcompany_idを更新しようとした場合、少なくとも1人のUSERがこの会社にリンクしていれば、エンジンはその操作を拒否します。
• 更新時に何もしない : RESTRICTと同じです。
• 更新カスケード時 : 通常、最も良いもの COMPANYテーブルの行のcompany_idを更新すると、エンジンはそのCOMPANYを参照しているすべてのUSERの行でそれを更新します（ただし、USERテーブルでトリガーを有効にしていない場合は警告が表示されます）。エンジンが変更を追跡してくれるので、良いですね。
• 更新時にNULLを設定 COMPANY テーブルの行の company_id を更新すると、エンジンは関連する USER の company_id を NULL に設定します (USER company_id フィールドで利用可能なはずです)。更新時にこのような処理を行う面白い方法は見当たりませんが、間違っているかもしれません。

そして、今度は ON DELETE の側です。

• 削除制限について : デフォルトの もし、COMPANYテーブルのcompany_idを削除しようとした場合、少なくとも1人のユーザーがその会社にリンクしていれば、エンジンはその操作を拒否しますので、あなたの命を救うことができます。
• 削除時に何もしない RESTRICTと同じです。
• 削除カスケード時 : 危険 COMPANYテーブルの会社の行を削除すると、関連するUSERも削除されます。これは危険ですが、セカンダリテーブルの自動クリーンアップに使用できます（したがって、COMPANY<->USERの例では確実に使用できません）。
• 削除時にNULLを設定 : 握り COMPANY行を削除した場合、関連するUSERは自動的にNULLになります。例えば、あるコンテンツの作者としてユーザーをアプリケーションに残す必要がある場合、会社を削除しても問題はありません。

通常、私のデフォルトは 削除時 制限時 更新時 カスケード .いくつかの ON DELETE CASCADE はトラックテーブル (ログ - すべてのログではない - など) 用に、そして ON DELETE SET NULL マスターテーブルが、USERテーブルに対するJOBテーブルのように、外部キーを含むテーブルの「単純な属性」である場合。

編集

久しぶりの書き込みです。今になって、一つ重要な警告を付け加えておこうと思います。MySQL には、カスケードに関する 1 つの大きな制限事項が文書化されています。 カスケードはトリガーを発射しない . ですから、もしあなたがそのエンジンに十分な自信を持ってトリガーを使っていたのなら、カスケード制約を避けるべきでしょう。

• http://dev.mysql.com/doc/refman/5.6/en/triggers.html

MySQLトリガーは、SQLステートメントによるテーブルの変更に対してのみ起動します。ビューの変更や、SQLステートメントをMySQLサーバーに送信しないAPIによって行われたテーブルの変更では起動しません。

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==> 最終編集の下をご覧ください、このドメインで物事が動いています。

トリガーは外部キー操作では起動しません。

そして、これはいつか修正されるとは思っていません。外部キー制約は InnoDb ストレージで管理され、トリガーは MySQL SQL エンジンで管理されます。両者は分離されています。Innodbは制約管理を持つ唯一のストレージで、いつかストレージエンジンで直接トリガーを追加するかもしれないし、しないかもしれない。

しかし、トリガーの実装の貧弱さと、非常に便利な外部キー制約のサポートのどちらを選ぶべきか、私自身の意見を持っています。そして、一度データベースの一貫性に慣れてしまえば、PostgreSQLを好きになることでしょう。

2017/12-MySQLについてこのEditを更新しました。

コメントで @IstiaqueAhmed が述べているように、この件に関する状況は変化しています。そのため、リンクをたどって本当の最新状況を確認してください（今後、また変わる可能性もあります）。