💨

TiDB の外部キーと外部キー制約について(2) ON UPDATE CASCADE と ON DELETE CASCADE

2025/03/05に公開

今日は外部キーと外部キー制約の２回目です。前回の記事で、TiDBは外部キーのスキーマは存在するけど、外部キー制約は存在しないというのは古い情報であり、現行Serverlessバージョン(7.1.3)でも外部キー制約は機能するという説明をしました。

一方前回の記事でも触れましたが、分散型NewSQLという技術特性上、外部キー制約におけるパフォーマンスの影響を通常のリレーショナルデータベースよりは多めに受けるようで、ご利用は計画的にという文言が公式ドキュメントにも丁寧に数か所記載されています。

一時的に外部キー制約をオフにするオプションとして以下が用意されています。

SET foreign_key_checks = 0;

今日は外部キー制約としての動作を見るためにON UPDATE CASCADEとON DELETE CASCADEを触ってみます。

ON UPDATE CASCADE / ON DELETE CASCADE とは

前回の例を再度以下にコピーします。

USE test;

-- 親テーブル
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

-- 子テーブル
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- まず親テーブルにデータを挿入
INSERT INTO categories (name) VALUES ('Example Category');

-- その後、子テーブルにデータを挿入
INSERT INTO products (category_id) VALUES (1);

子テーブルのcategory_idは親テーブルのidと外部キー制約で紐付いており、親テーブルに存在しないidの値を子テーブルのcategory_idに挿入することはできません。
この際親テーブルのidにアップデートがかかった際に子テーブルのcategory_idとの紐づけがずれないように、一緒に子テーブルのcategory_idを更新してあげるのが、ON UPDATE CASCADEです。それと似ていて削除時に、同時に子テーブルも削除してあげるのがON DELETE CASCADEです。

さっそくやってみる

ON UPDATE CASCADE

まずはCASCADEなしで以下のクエリを実行してみます。

USE test;

-- 親テーブル
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

-- 子テーブル
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- まず親テーブルにデータを挿入
INSERT INTO categories (name) VALUES ('Example Category');

-- まず子テーブルにデータを挿入
INSERT INTO products (category_id) VALUES (1);

UPDATE categories SET id = 2 WHERE id = 1;

すると期待通りUPDATE時にエラーで処理が停止します。

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`products`, CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`))

UPDATEを実行すると子テーブルがさす親テーブルのレコードがなくなるためです。
ちなみに前述の通りUPDATE分を以下に変更すると処理は問題なく行われます。

SET foreign_key_checks = 0;
UPDATE categories SET id = 2 WHERE id = 1;
SET foreign_key_checks = 1;

ちなみにSET foreign_key_checksはセッション変数として設定が存続するため、drop table やcreate tableでも設定はリセットされませんのでテストの際は注意してください。
では次に以下を実行します。

/* Enter "USE {database};" to start exploring your data.
   Press Ctrl + I to try out AI-generated SQL queries or SQL rewrite using Chat2Query. */

USE test;

drop TABLE test.products;
drop TABLE test.categories;

-- 親テーブル
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

-- 子テーブル
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON UPDATE CASCADE
);

-- まず親テーブルにデータを挿入
INSERT INTO categories (name) VALUES ('Example Category');

-- まず子テーブルにデータを挿入
INSERT INTO products (category_id) VALUES (1);

UPDATE categories SET id = 2 WHERE id = 1;

select * from products;

親テーブルに対するUPDATEを受け、子テーブルの値が以下になります。

ON DELETE CASCADE

DELETEも同じ動作になります。

/* Enter "USE {database};" to start exploring your data.
   Press Ctrl + I to try out AI-generated SQL queries or SQL rewrite using Chat2Query. */

USE test;

drop TABLE test.products;
drop TABLE test.categories;

-- 親テーブル
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

-- 子テーブル
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- まず親テーブルにデータを挿入
INSERT INTO categories (name) VALUES ('Example Category');

-- まず子テーブルにデータを挿入
INSERT INTO products (category_id) VALUES (1);

DELETE from categories;

select * from products;

親テーブルのレコード削除のタイミングで子テーブルの外部キーで紐づくレコードが削除されています。

INSERT CASCADE

INSERT処理はそもそも毎回子テーブルに挿入される値が親テーブルに存在しているか？をデフォルトで確認を行い、親テーブルに存在していない場合エラーとなり処理が止まります。

INSERT INTO products (category_id) VALUES (1);
Cannot add or update a child row: a foreign key constraint fails (`test`.`products`, CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

このため、デフォルトでチェックが行われているため、あえてCASCADEを宣言する必要はありません。

CASCADE と RESTRICT

上記の動作で見たようにCASCADEとは親テーブルへの操作時に、同時に子テーブルのレコードを操作してくれるので矛盾を防ぐためとても便利ですが、当然パフォーマンスには注意する必要がります。対して矛盾が所持そうな場合その処理を行わないのでRESTRICTです。これは親テーブルに対してUPDATEやDELETEが発生した際に、子テーブルへの矛盾が生じることが分かった場合、子テーブルのレコードを書き換えるのではなく処理を停止させます。
これはあえて宣言しなくてもデフォルトの外部キー制約の動作になります。