MySQLで学ぶ受注管理システム開発（2）「CRUD操作を極めよう」

開発・動作確認環境：GitHub Codespaces（以前構築した環境を使用）

はじめに
CRUDとトランザクションの関係
新規登録（INSERT）実践
更新（UPDATE）実践
1. 注文ステータスの更新
2. 複数列をまとめて更新する
削除（DELETE）実践
1. 外部キー制約でエラーになるケース
2. 論理削除という選択肢
動作確認
GitHubへの反映
まとめ
コードのダウンロード
1. 関連記事
2. 関連

はじめに

前回は、「MySQLで学ぶ受注管理システム開発（1）「テーブル設計とER図を描こう」」にて、顧客・商品・注文・注文明細という4テーブルの設計を行った。今回は、このテーブルに対してINSERT・UPDATE・DELETEを実践し、あわせてトランザクションを使ったデータ整合性の担保についてご紹介しよう。

前回構築したorder_dbデータベースがそのまま使える前提で進める。
もし削除してしまった場合は、前回記事のSQLを再実行してから読み進めてほしい。

CRUDとトランザクションの関係

CRUD（Create・Read・Update・Delete）は、データベース操作の基本4種類を指す。
前回はSELECT（Read）を中心に扱ったので、今回は残りのCreate・Update・Deleteを扱う。

ここで重要になるのが、トランザクションの考え方である。例えば「注文を1件登録する」という一見単純な処理も、実際には以下の複数のSQL文から成り立っている。

orders表に注文ヘッダーを1行追加する
order_details表に注文明細を複数行追加する
products表の在庫数を減らす

このうち2番目や3番目の途中でエラーが起きた場合、1番目だけが実行されて「中身のない注文」がデータベースに残ってしまうと、データの整合性が崩れる。トランザクションは、このような「複数の処理をすべて成功させるか、すべて取り消すか」を制御する仕組みである。

新規登録（INSERT）実践

顧客・商品の追加

まずは、シンプルな1件のINSERTから確認しよう。

USE order_db;

-- 新規顧客の登録
INSERT INTO customers (customer_id, customer_name, email, phone) VALUES
    ('C004', '高橋健太', 'takahashi@example.com', '090-4444-4444');

-- 新規商品の登録
INSERT INTO products (product_id, product_name, unit_price, stock_quantity) VALUES
    ('P004', 'ワイヤレスキーボード', 3800.00, 30);

実行例

mysql> INSERT INTO customers (customer_id, customer_name, email, phone) VALUES
    -> ('C004', '高橋健太', 'takahashi@example.com', '090-4444-4444');
Query OK, 1 row affected (0.01 sec)

注文をトランザクションでまとめて登録する

ここからが今回の本題である。「注文ヘッダーの登録」「注文明細の登録」「在庫数の減算」という3つの処理を、1つのトランザクションとしてまとめて実行しよう。

START TRANSACTION;

-- 1. 注文ヘッダーを登録
INSERT INTO orders (order_id, customer_id, status) VALUES
    ('O004', 'C004', '受付');

-- 2. 注文明細を登録（ワイヤレスキーボードを2個注文）
INSERT INTO order_details (order_id, product_id, quantity, unit_price_at_order) VALUES
    ('O004', 'P004', 2, 3800.00);

-- 3. 在庫数を減算
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 'P004';

-- ここまでの処理をすべて確定させる
COMMIT;

実行例

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO orders (order_id, customer_id, status) VALUES ('O004', 'C004', '受付');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO order_details (order_id, product_id, quantity, unit_price_at_order) VALUES ('O004', 'P004', 2, 3800.00);
Query OK, 1 row affected (0.01 sec)

mysql> UPDATE products SET stock_quantity = stock_quantity - 2 WHERE product_id = 'P004';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

ポイント

START TRANSACTIONからCOMMITまでの間に実行したSQL文は、COMMITを実行するまでは仮の状態であり、他のセッションからは見えない。COMMITして初めて、変更が確定してデータベースに反映される。

在庫不足時のROLLBACK

次に、あえて在庫が足りないケースを想定し、途中で処理を取り消す（ROLLBACKする）例を確認しよう。ノートパソコン（P001）の在庫は前回時点で15個だが、100個の注文が来たと仮定する。

START TRANSACTION;

-- 在庫を確認する
SELECT stock_quantity FROM products WHERE product_id = 'P001';

実行例

mysql> SELECT stock_quantity FROM products WHERE product_id = 'P001';
+----------------+
| stock_quantity |
+----------------+
|             15 |
+----------------+
1 row in set (0.00 sec)

在庫が15個しかなく、100個の注文には対応できないことが分かった。このような場合は、注文ヘッダーや明細を登録せずに、トランザクションを取り消す。

-- 在庫不足のため、ここまでの処理をすべて取り消す
ROLLBACK;

実行例

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

このように、アプリケーション側で「在庫が足りるかどうか」をチェックし、足りなければROLLBACK、足りればCOMMITという分岐を実装するのが、実務における一般的なパターンである。

更新（UPDATE）実践

注文ステータスの更新

注文が処理された段階で、ステータスを更新する例を確認しよう。

-- O002を出荷済みに更新
UPDATE orders
SET status = '出荷済'
WHERE order_id = 'O002';

実行例

mysql> UPDATE orders SET status = '出荷済' WHERE order_id = 'O002';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

【注意】UPDATE文でWHERE句を書き忘れると、テーブル内の全行が更新されてしまう。実務でも起きがちな事故なので、UPDATEを書くときは、まずSELECT * FROM orders WHERE order_id = 'O002';のように同じ条件でSELECTを実行し、更新対象が意図通りの行だけであることを確認してからUPDATEに切り替える習慣をつけよう。

複数列をまとめて更新する

-- 商品の価格と在庫を同時に更新
UPDATE products
SET unit_price = 2300.00,
    stock_quantity = stock_quantity + 20
WHERE product_id = 'P003';

削除（DELETE）実践

外部キー制約でエラーになるケース

前回、order_details表はorders表を外部キーで参照する設計にした。この状態で、明細が残っている注文をそのまま削除しようとすると、どうなるか確認しよう。

DELETE FROM orders WHERE order_id = 'O001';

実行例

mysql> DELETE FROM orders WHERE order_id = 'O001';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`order_db`.`order_details`, CONSTRAINT `order_details_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`))

エラーになった。これは、外部キー制約が正しく機能している証拠である。「注文明細だけが残った、宙に浮いた状態」を防いでくれている。削除する場合は、子テーブル（order_details）から先に削除する必要がある。

-- 先に子テーブル（注文明細）を削除
DELETE FROM order_details WHERE order_id = 'O001';

-- その後、親テーブル（注文）を削除
DELETE FROM orders WHERE order_id = 'O001';

論理削除という選択肢

ただし、実務では「注文データを物理的に消してしまう」ことは滅多にない。過去の売上記録として残しておく必要があるためだ。そこで、実際に行を削除する代わりに、ステータスを「キャンセル」に更新するという論理削除の考え方がよく使われる。

-- 物理削除ではなく、ステータス変更で対応する
UPDATE orders
SET status = 'キャンセル'
WHERE order_id = 'O003';

こうしておけば、後から「キャンセルされた注文の一覧」を集計するといったことも可能になる。DELETEを使うか、論理削除にするかは、そのデータが将来的に参照される可能性があるかどうかで判断するとよい。

動作確認

一連の操作の結果を確認しよう。

SELECT order_id, customer_id, status FROM orders ORDER BY order_id;

実行例

mysql> SELECT order_id, customer_id, status FROM orders ORDER BY order_id;
+----------+-------------+-----------+
| order_id | customer_id | status    |
+----------+-------------+-----------+
| O002     | C002        | 出荷済    |
| O003     | C001        | キャンセル|
| O004     | C004        | 受付      |
+----------+-------------+-----------+
3 rows in set (0.00 sec)

O001は物理削除され、O003は論理削除（キャンセル）としてステータスのみ変更されていることが確認できる。

GitHubへの反映

今回作成したSQLをmysql-learningリポジトリに追加する。

mysql-learning/
├── .devcontainer/
├── FE-OPEN-R7/
├── order-management/
│   ├── 01_schema.sql
│   └── 02_crud.sql   ← 今回作成したCRUD＆トランザクション実践
├── README.md
└── SETUP.md

git add .
git commit -m "CRUD操作とトランザクションの実践例を追加（第2回）"
git push

まとめ

今回は、前回設計したテーブルに対して、以下を学んだ。

INSERT・UPDATE・DELETEの基本操作
START TRANSACTION〜COMMIT／ROLLBACKによるトランザクション制御
外部キー制約が、データの整合性を守ってくれる仕組み
物理削除と論理削除の使い分け

次回は、今回のような集計・整合性チェックを毎回手で書く代わりに、ビュー（VIEW）とストアドプロシージャを使って自動化・共通化する方法を紹介しよう。

コードのダウンロード

今回のコードは、私のGitHubリポジトリからダウンロードできる。

▶ Rocky-Seven/mysql-learning/order-management

MySQLで学ぶ受注管理システム開発（1）「テーブル設計とER図を描こう」

月	火	水	木	金	土	日
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31