C言語＋MySQL入門｜テーブルと主キーを復習する

C言語＋MySQL入門｜テーブルと主キーを復習する

前の記事では、在庫管理用のデータベースであるinventory_dbを作成しました。

次は、その中へ商品情報や在庫履歴を保存するテーブルを用意していきます。その前に、SQLite編でも登場したテーブル・行・列と、主キーの役割を復習しましょう。

テーブルを作るときは、項目を並べることに加えて、一つの行が何を表し、その行をどの値で識別するのかを考えることが大切です。

この記事では、次の内容を扱います。

• テーブル・行・列の関係
• 列ごとにデータ型や条件を決める理由
• 商品を識別するために主キーが必要な理由
• 主キーの重複とNULLに関するルール
• 主キーと自動採番の違い
• 商品IDと、画面に表示される順番の違い

今回は、商品データの例を使って設計の考え方を確認します。実際にproductsを作成するSQLは、次の記事で扱います。

テーブルは同じ種類の情報を整理する場所

テーブルは、行と列を使ってデータを整理する仕組みです。

在庫管理アプリでは、商品情報をproductsに、入庫・出庫の履歴をstock_historyに保存する予定です。

同じ在庫管理に関する情報でも、商品そのものの情報と、在庫が動いた記録では、一つの行が表す内容が異なります。

この違いを意識して分けると、データを整理しやすくなります。

商品テーブルのイメージ

たとえば、productsには次のような情報を保存します。

この表では、三つの商品を管理しています。

単価は円、在庫数は個数として考えます。ここで示すデータは説明用の例であり、前の記事で作成したinventory_dbに、すでに登録されているわけではありません。

行は一件分のデータを表す

行は、表の横方向のまとまりです。レコードと呼ばれることもあります。

商品テーブルなら、次の一行が一つの商品を表します。

この一行には、次の情報がまとまっています。

• 商品IDは101
• 商品名はノート
• 単価は180円
• 在庫数は24個

ここで、商品テーブルの一行は、倉庫にある現物一個を表しているわけではありません。

この教材では、管理対象となる一つの商品に一行を用意し、その商品の数量を在庫数で表します。そのため、ノートが24個あるからといって、同じ商品の行を24行作るわけではありません。

一行で何を表すのかを決めることが、テーブル設計の基本です。

列はデータの項目を表す

列は、表の縦方向のまとまりです。カラムとも呼ばれます。

商品ID、商品名、単価、在庫数が、それぞれ列に当たります。

先ほどの表では見やすくするために登録日時を省いていますが、実際の商品テーブルでは登録日時も用意します。

列の名前と役割を決めておくことで、どの値が何を意味するのかを整理できます。

図1：商品テーブルの行と列

この図から分かること

行は一つの商品、列は商品名や在庫数などの項目を表します。

それらをまとめたものが商品テーブルです。

在庫数が24の商品も、一つの商品として一行で管理します。行数と在庫の個数は、意味が異なります。

列にはデータ型と条件を設定する

テーブルの列には、名前だけでなく、保存する値に合ったデータ型を設定します。

C言語でも、整数、文字、文字列などを区別して扱いました。データベースでも、項目に合った型を選ぶ必要があります。

たとえば、在庫数には個数を保存したいので、数字以外の自由な文章を入れる項目にはしません。

また、数値型を選ぶだけで、在庫管理として必要な条件がすべて決まるわけではありません。負の在庫を禁止するなどのルールは、データ型や制約、アプリの処理を組み合わせて実現します。

データのルールを制約で表す

制約は、テーブルへ保存するデータが守るべきルールです。

今は、すべての書き方を覚えなくても大丈夫です。

この記事では、行を識別するためのPRIMARY KEYを中心に見ていきましょう。

主キーは一つの行を識別するための目印

主キーは、テーブルの中で一つの行を一意に識別するための列、または列の組み合わせです。

一意とは、ほかと重ならず、対象を一つに決められることです。

今回の商品テーブルでは、商品IDを主キーとして使う方針にします。SQL上の列名はproduct_idです。

商品IDが101と指定されれば、どの商品を対象にするのかを決められます。

ただし、その商品が削除されていれば、該当する行はありません。主キーが保証するのは、同じ値を持つ行が複数存在しないことです。

商品名だけでは対象を決めにくい

商品名を使えば商品を見分けられそうですが、名前だけで一つに決められるとは限りません。

たとえば、仕様が異なる商品を同じ名前で登録する場合を考えます。

この状態でノートの単価を変更したいと言われても、どちらの商品なのかを判断できません。

一方、商品IDが104の商品と指定すれば、対象を区別できます。

名前の変更と商品の識別を分ける

商品名は、後から分かりやすい表記へ変更することもあります。

名前を変えても、管理対象としては同じ商品です。そのため、今回のアプリでは、商品名の変更に合わせて商品IDを変更する必要はありません。

主キーには、このように日常の編集で変えずに使える値を選ぶと、商品や履歴を扱いやすくなります。

MySQLの主キーが守るルール

MySQLでは、主キーに重複した値を保存できず、主キーを構成する列にNULLを保存することもできません。また、一つのテーブルに定義できる主キーは一つです。

これらのルールは、主キーとして定義することで、データベース側でも確認されます。

同じ商品IDを別の商品へ使えない

商品IDが101の行がすでにある状態で、別の商品を同じ101で通常のINSERTにより追加しようとすると、主キーの重複としてエラーになります。

なお、重複していないからといって、登録全体が必ず成功するわけではありません。ほかの列の型や制約も満たす必要があります。

NULLは0や空文字とは異なる

NULLは、値が存在しない、または不明であることを表すものです。

数値の0や、長さが0の文字列とは区別します。

主キーは対象を識別するための値なので、NULLのまま保存することはできません。

複数の列を組み合わせる主キーもある

主キーは、必ず一列だけとは限りません。

たとえば、倉庫ごとの在庫を管理する別の設計では、倉庫IDと商品IDを組み合わせて、一行を識別する方法があります。

このような主キーを複合主キーと呼びます。複合主キーでは、列の組み合わせ全体が重複しないようにします。

今回は基本をつかみやすくするため、productsでは商品ID一列を主キーにします。

図2：商品名が同じでも主キーで区別できる

この図から分かること

商品名が同じでも、商品IDが異なれば別の商品として識別できます。

主キーは、利用者に分かりやすい名前とは別に、処理対象を確実に指定するための役割を持ちます。

ただし、主キーが異なれば、同じ商品を誤って二重登録することまで自動的に防げるわけではありません。業務上の重複を防ぐルールは、必要に応じて別に考えます。

主キーと自動採番は役割が違う

商品IDを使うとき、毎回自分で番号を決めるのは手間がかかります。

MySQLには、整数の列へ新しい値を自動的に割り当てるAUTO_INCREMENTがあります。

ここで、PRIMARY KEYとAUTO_INCREMENTを分けて覚えましょう。

主キーを付けただけで、自動採番まで行われるわけではありません。

今回の商品IDでは、主キーと自動採番を組み合わせる方針です。具体的な定義は、次の記事で確認します。

商品IDは必ずしも連続しない

自動採番では、番号が途中で抜けることがあります。

たとえば、登録済みの行を削除すれば、その番号の行は一覧からなくなります。また、InnoDBでは、採番後にトランザクションを取り消した場合などにも欠番が生じます。

このような並びになっていても、商品IDが重複せず、それぞれの商品を識別できていれば、欠番だけを理由に異常とは判断しません。

商品IDは、商品の件数を表すものでもありません。この例の商品の件数は3件であり、最大の商品IDである5とは異なります。

欠番を埋める目的で、残っている商品のIDを振り直す必要はありません。

商品IDは画面の行番号ではない

主キーを使ううえで、もう一つ大切なのが表示順との違いです。

商品一覧は、単価の高い順や商品名順など、さまざまな順序で表示できます。

たとえば、次の商品データを考えます。

これを単価の安い順に並べると、次のようになります。

ノートの表示位置は1行目から3行目へ変わりましたが、商品IDは101のままです。

表示する場所が変わっても、商品の識別に使う値は変わりません。

主キーがあっても表示順は別に指定する

主キーを設定しただけで、検索結果が必ず商品ID順に表示されるとは限りません。

SQLで結果の順序を指定するときは、ORDER BYを使います。

商品IDが小さい順に並べることと、商品IDで行を識別することは、別の役割です。

検索や並び替えの具体的な書き方は、商品を登録・表示する記事で扱います。

C言語の配列番号とも区別する

Cプログラムで検索結果を配列へ保存する場合も、配列の添字と商品IDは別です。

単価の安い順に取得した結果を配列へ入れたなら、添字0の要素に商品ID103の商品が入ることもあります。

後でGUIの商品一覧を作るときも、選択した行の位置から商品IDを決めつけず、その行に対応する商品IDを取得して操作します。

図3：表示順が変わっても商品IDは変わらない

この図から分かること

並び替えによって変わるのは、検索結果や画面上の表示位置です。

ノートが1行目から3行目へ移動しても、商品IDは101のままです。

変更や削除、入庫・出庫では、画面の何行目かではなく、その行に対応する商品IDを使って対象を指定します。

主キーは変更や削除の対象を決めるときにも使う

在庫管理アプリでは、商品IDを使って操作対象を指定します。

ただし、主キーがあるだけで、すべての更新や削除が自動的に一件へ限定されるわけではありません。

UPDATEやDELETEでは、どの行を対象にするのかをWHERE条件で指定します。条件を書かなければ、複数の行やすべての行が対象になることがあります。

主キーを用意することと、SQLで対象を正しく絞り込むことの両方が必要です。

商品と在庫履歴をつなぐ目印にもなる

商品IDは、別のテーブルに保存する履歴と商品を対応付けるときにも使います。

たとえば、商品ID101のノートに対して、入庫と出庫を行ったとします。

この例では、商品ID101が履歴の中に二回登場します。

それぞれ別の入出庫なので、履歴側の商品IDが繰り返されることには意味があります。

商品の表では一つの商品IDに一行を対応させ、履歴の表では同じ商品に複数の記録を対応させます。

この関係をデータベースの制約として設定する方法は、3.7の商品と履歴を関連付ける記事で扱います。

SQLite編の考え方をMySQLでも使おう

SQLite編で学んだ、テーブルに情報を整理し、主キーで行を識別する考え方は、MySQLでも役立ちます。

一方、型の扱いや自動採番の指定などには、データベース製品ごとの違いがあります。

SQLiteのSQLをそのまま書き写すのではなく、共通する考え方を土台にして、MySQLでの書き方を確認していきましょう。

次は商品テーブルを作成する

次の記事では、inventory_dbの中にproductsを作成します。

商品ID、商品名、単価、在庫数、登録日時の各列について、型と条件を決め、CREATE TABLEで定義します。

今回確認した主キーと自動採番を商品IDへ設定し、一つの商品を確実に識別できるテーブルを作っていきましょう。

Follow me!

おすすめ記事

C言語＋MySQL入門｜今回作成する在庫管理アプリ C言語＋MySQL入門｜MySQLとは 6日でできる 新C言語入門｜構造体の総合演習 6日でできる 新C言語入門｜構造体でデータをまとめる方法 C言語のきほん｜ポインタで構造体を操作する 【6日でできるPHP入門】更新と削除 Java道｜Javaの文字コードのしくみ Java超｜Javaの文字コードの扱い方