C言語＋MySQL入門｜商品と履歴を関連付ける

C言語＋MySQL入門｜商品と履歴を関連付ける

これまでの記事で、商品情報を保存するproductsと、入出庫を記録するstock_historyを作成しました。

今回は、2つのテーブルをproduct_idで関連付けます。外部キー制約を設定し、存在しない商品の履歴を登録できないようにしましょう。

あわせて、商品を削除するときに履歴をどう扱うかも決めます。今回の在庫管理アプリでは、履歴が残っている商品は削除できない設計にします。

この記事では、次の内容を学びます。

• 商品と在庫履歴の1対多の関係を理解する。
• 主キーと外部キーの役割を区別する。
• 商品を削除するときの動作を決める。
• ALTER TABLEで外部キー制約を追加する。
• 関連付けと削除ルールを確認する。

この記事では、前の記事までに作成した2つのテーブルをそのまま使います。

1つの商品には複数の履歴がある

商品と在庫履歴の関係を、もう一度確認してみましょう。

productsでは、1行が1つの商品を表します。一方、stock_historyでは、1行が1回の入出庫を表します。

同じ商品を何度も入出庫すれば、その商品の履歴は増えていきます。

たとえば、商品IDが101のノートに対して、次のような履歴があるとします。

3件の履歴は、いずれも同じ商品を参照しています。

このように、1つの商品に複数の履歴が対応する関係を、1対多の関係と呼びます。

ただし、登録したばかりの商品には履歴がないこともあります。今回の設計をより正確に表すと、次のようになります。

• 1つの商品には、0件以上の履歴が対応する。
• 1件の履歴は、必ず1つの商品に対応する。

ここで示した商品IDや履歴は、関係を説明するための例です。実際のデータ登録は、後の記事で行います。

図1：1つの商品に複数の在庫履歴が対応する

この図から分かること

履歴ごとのhistory_idは異なりますが、対象商品が同じであればproduct_idは共通です。

商品と履歴を結び付けるのはproduct_idです。history_idは、stock_historyの中で個々の履歴を識別するために使います。

主キーと外部キーの役割を区別しよう

今回の関連付けでは、次の3つの列の役割を押さえておきましょう。

主キーは、そのテーブルの行を一意に識別するためのものです。

外部キーは、別のテーブルなどのキーを参照するためのものです。外部キー制約を設定すると、参照先との関係が正しいかをMySQLに検査させることができます。

今回、参照されるproductsを親テーブル、参照するstock_historyを子テーブルと呼びます。外部キー制約は、子テーブル側に定義します。

同じ列名だけでは関連付けにならない

前の記事で、両方のテーブルにproduct_idを用意しました。

しかし、列名が同じだけでは、MySQLは2つの列の関係を自動的には判断しません。

たとえば、productsに商品ID999が存在しなくても、外部キー制約がなければ、stock_historyのproduct_idに999を保存できてしまいます。

そのような履歴があると、どの商品についての記録なのか分からなくなります。

そこで、stock_historyのproduct_idには、productsのproduct_idに存在する値を保存するというルールを追加します。このように、参照関係の整合性を保つことを参照整合性と呼びます。

外部キーの値は重複してもよい

今回のstock_historyのproduct_idには、同じ商品IDを繰り返し保存できます。

商品ID101について何度入出庫しても、それぞれ別のhistory_idを持つ履歴として記録できます。

外部キーは、その列の値を一意にする設定ではありません。参照先に対応する値が存在することを確認するための設定です。

また、stock_historyのproduct_idには、すでにNOT NULLを設定しています。外部キー制約と組み合わせることで、各履歴には必ず実在する商品を指定する設計になります。

商品を削除するときのルールを決めよう

商品と履歴を関連付けるときには、商品を削除しようとした場合の動作も決めます。

たとえば、商品ID101に履歴が3件ある状態で、productsから商品ID101を削除したら、履歴をどう扱うべきでしょうか。

代表的な選択肢には、次のようなものがあります。

SET NULLを使用するには、子テーブル側の列がNULLを許可している必要があります。今回のstock_historyのproduct_idはNOT NULLなので、この設計のままでは使えません。

今回はRESTRICTで履歴のある商品を残す

今回のアプリでは、ON DELETE RESTRICTを選びます。

入出庫の履歴は、過去の在庫の動きを確認するための記録です。商品を削除しただけで履歴まで消えると、その商品の入出庫を後から調べられなくなってしまいます。

そのため、今回の方針は次のようにします。

• 履歴のある商品は、削除を拒否する。
• 商品と履歴の両方を残す。
• 履歴のない商品は、この外部キー制約によっては削除を妨げない。

ここで大切なのは、RESTRICTは、商品を削除して履歴だけ残す設定ではないという点です。

対応する履歴がある場合は、商品の削除そのものが失敗します。

将来、取り扱いを終了した商品を画面に表示したくなった場合は、商品を削除する代わりに、使用中かどうかを表す列を追加する方法などを考えられます。今回は、削除を制限する基本の設計で進めましょう。

商品IDの変更も制限する

今回は、ON UPDATEにもRESTRICTを指定します。

これにより、履歴から参照されているproductsのproduct_idを、別の値へ変更できないようにします。

商品IDは、商品を安定して識別するための番号として扱うためです。

ただし、この設定は、商品情報の更新をすべて禁止するものではありません。

それぞれの更新では、NOT NULLやCHECKなど、ほかの制約も満たす必要があります。

ALTER TABLEで外部キー制約を追加しよう

すでに存在するテーブルの構造や制約を変更するときは、ALTER TABLEを使います。

今回は、stock_historyに外部キー制約を追加します。productsやstock_historyを作り直す必要はありません。

MySQLクライアントへ管理作業用のアカウントで接続し、次のSQLを実行しましょう。

商品と在庫履歴を関連付けるSQL

ファイル名： add_stock_history_foreign_key.sql

USE inventory_db;

ALTER TABLE stock_history
    ADD CONSTRAINT fk_stock_history_product
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

実行結果の例

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

処理時間や表示される件数は、テーブルの状態によって異なります。これは、前の記事までの手順どおり、商品と履歴がまだ登録されていない状態での例です。

Query OKが表示されれば、制約の追加は成功です。

このSQLは、同じ制約を繰り返し追加するためのものではありません。一度成功した後は、再実行する代わりに、後で紹介する確認用SQLを使いましょう。

外部キーを追加するSQLを読み解こう

今回のSQLを、役割ごとに分けて見てみます。

fk_stock_history_productは、今回付けた制約名です。

名前から、stock_historyとproductsの関連付けに使う制約だと分かるようにしています。制約を確認したり、エラーの原因を調べたりするときにも使われます。

関連付ける列の型をそろえる

今回のproduct_idは、両方のテーブルで符号付きのINTです。また、両方のテーブルでInnoDBを使用しています。

外部キーで対応する整数列は、型のサイズと符号の有無をそろえる必要があります。片方がINT、もう片方がINT UNSIGNEDになっている場合などは、定義を見直す必要があります。

前の記事までのSQLどおりに作成していれば、この条件は満たしています。

図2：履歴がある商品の削除をRESTRICTで止める

この図から分かること

履歴が残っている商品を削除しようとすると、RESTRICTによって削除が拒否されます。

商品と履歴の両方が残るため、履歴の参照先が失われません。外部キー制約は、このように親テーブル側の操作にも関係します。

追加した関連付けを確認しよう

SHOW CREATE TABLEで外部キーの定義を確認する

まず、stock_historyの定義全体を表示します。

SHOW CREATE TABLE stock_history\G

実行結果の例

*************************** 1. row ***************************
       Table: stock_history
Create Table: CREATE TABLE `stock_history` (
  `history_id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `movement_type` varchar(10) NOT NULL,
  `quantity` int NOT NULL,
  `recorded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`history_id`),
  KEY `fk_stock_history_product` (`product_id`),
  CONSTRAINT `fk_stock_history_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_stock_history_quantity` CHECK ((`quantity` > 0)),
  CONSTRAINT `chk_stock_history_type` CHECK ((`movement_type` in (_utf8mb4'IN',_utf8mb4'OUT')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

表示の細かな整形や制約の順序などは、環境によって異なる場合があります。

前の記事の定義と比べると、主に次の内容が追加されています。

• product_idのインデックス。
• fk_stock_history_productという外部キー制約。
• productsのproduct_idを参照する指定。
• 削除時と更新時のRESTRICT。

KEYの行は、外部キーの検査に必要なインデックスです。今回のように適切なインデックスが存在しない場合、MySQLが子テーブル側に作成します。

なお、インデックスと外部キー制約は別のものです。関連付けの確認では、KEYの行だけでなく、CONSTRAINTから始まるFOREIGN KEYの定義を確認してください。

削除時と更新時のルールを一覧で確認する

MySQLには、データベースの構造や制約を調べるためのINFORMATION_SCHEMAがあります。

次のSQLでは、追加した外部キー制約の削除ルールと更新ルールを確認します。

SELECT
    CONSTRAINT_NAME,
    TABLE_NAME,
    REFERENCED_TABLE_NAME,
    DELETE_RULE,
    UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_NAME = 'stock_history'
  AND CONSTRAINT_NAME = 'fk_stock_history_product';

実行結果の例

+--------------------------+---------------+-----------------------+-------------+-------------+
| CONSTRAINT_NAME          | TABLE_NAME    | REFERENCED_TABLE_NAME | DELETE_RULE | UPDATE_RULE |
+--------------------------+---------------+-----------------------+-------------+-------------+
| fk_stock_history_product | stock_history | products              | RESTRICT    | RESTRICT    |
+--------------------------+---------------+-----------------------+-------------+-------------+
1 row in set (0.00 sec)

各項目は、次の情報を示しています。

REFERENTIAL_CONSTRAINTSには、外部キーの参照先や削除・更新ルールが記録されています。

DELETE_RULEとUPDATE_RULEの両方がRESTRICTになっていれば、今回の指定を確認できています。

SQL中のDATABASE()は、現在選択しているデータベース名を返します。確認前にUSE inventory_dbを実行しておきましょう。

外部キーを設定すると操作はどう変わる？

設定後の動作を、具体的な場面で整理してみます。

次の表は、外部キーの検査が有効な通常の状態で、ほかの制約も満たしている場合の動作です。

存在しない商品を参照する履歴の追加・変更では、ERROR 1452が発生します。履歴に参照されている商品の削除・商品ID変更では、ERROR 1451が発生します。

これは、データベースが壊れたという意味ではありません。設定したルールによって、不整合が生じる操作を止めている状態です。

CプログラムからSQLを実行する場合も同じです。将来のアプリでは、SQLの実行結果を確認し、商品を削除できなかった理由などを利用者へ伝える処理が必要になります。

商品を先に登録し、その後で履歴を登録する

外部キーを設定した後は、履歴の参照先となる商品が先に存在している必要があります。

そのため、今後のデータ登録は、次の順番で進めます。

1. productsに商品を登録する。
2. 登録されたproduct_idを確認する。
3. そのproduct_idを指定してstock_historyに履歴を登録する。

履歴を先に登録して、後から商品を用意する手順にはしません。

次の記事では、この最初の段階にあたる商品の登録を行います。

外部キーが守る範囲を理解しておこう

外部キーは、商品と履歴の参照関係を守る仕組みです。在庫管理に必要な処理を、すべて自動化するわけではありません。

今回の設定でも、次の点は別に扱う必要があります。

また、ON DELETE RESTRICTが制限するのは、履歴から参照されている商品の削除です。stock_historyの行そのものを削除・変更できなくする設定ではありません。

たとえば、すべての履歴が別の操作で削除されれば、その商品を参照する履歴はなくなります。履歴を長く保存する運用には、アプリの操作設計や権限設計も関係します。

今回の外部キー制約は、その土台となる参照関係のルールとして捉えておきましょう。

図3：外部キーが商品の存在と参照関係を確認する

この図から分かること

外部キー制約は、履歴を登録するときに参照先の商品が存在することを確認します。

さらに、今回選んだRESTRICTによって、履歴が参照している商品の削除も止めます。子テーブル側と親テーブル側の両方の操作に対して、関連付けを守るルールが働きます。

外部キーを追加できないときは

外部キー制約の追加に失敗した場合は、次の点を確認しましょう。

前の記事までの手順どおりであれば、2つのテーブルは空なので、既存データの不整合はありません。

独自にデータを追加している場合は、存在しない商品を参照する履歴があると、通常の外部キー検査が有効な状態では制約を追加できません。対象の履歴と商品を確認し、データの意味に沿って修正してください。

また、すでに追加済みの制約がある場合は、今回のSQLを繰り返す必要はありません。定義を確認し、参照先とRESTRICTの設定が正しければ、そのまま次へ進めます。

次は商品を登録して表示しよう

productsとstock_historyに、商品IDを通じた参照関係を設定できました。履歴が参照する商品の存在を確認でき、履歴のある商品の削除も制限する構成です。

次の記事では、INSERTでproductsに商品を登録し、SELECTで保存されたデータを表示します。テーブルの設計から、実際のデータ操作へ進んでいきましょう。

Follow me!

おすすめ記事

C言語＋MySQL入門｜接続ユーザーの権限を確認する C言語＋MySQL入門｜在庫履歴テーブルを作成する C言語＋MySQL入門｜商品を変更・削除する C言語＋MySQL入門｜商品を登録・表示する C言語＋MySQL入門｜商品テーブルを作成する C言語のきほん｜関数とポインタのしくみ C言語DB入門｜入力エラーとSQLiteのエラーに対応する Ｃ＋＋入門｜4章の練習問題