SQL基礎~基本的なSQL概念2~

今回は、SQLの基礎としてデータ型、主キー、外部キーについて解説します。

データ型

データベースに保存するデータにはさまざまな種類があり、それぞれ適切なデータ型を選択することが重要です。

SQLの種類によって若干言葉は異なりますが、主なデータ型は以下の通りです。

数値型

　　• INT：整数を表します。例：42

　　• FLOAT、DOUBLE：浮動小数点数を表します。例：3.14

　　• DECIMAL：正確な小数点数を表します。例：19.99

文字列型

　　• CHAR：固定長の文字列を表します。例：CHAR(10)は10文字固定。

　　• VARCHAR：可変長の文字列を表します。例：VARCHAR(255)は最大255文字。

　　• TEXT：長い文字列を表します。例：ブログ記事の内容。

日付型

　　• DATE：日付を表します。例：2024-06-11

　　• TIME：時間を表します。例：14:30:00

　　• DATETIME：日付と時間を組み合わせたものを表します。例：2024-06-11 14:30:00

　　• TIMESTAMP：日付と時間を表し、タイムゾーンを含む場合もあります。

その他のデータ型

　　• BOOLEAN：真偽値を表します。例：TRUE、FALSE

　　• BLOB：バイナリデータを表します。例：画像や音声ファイル

主キーと外部キー

データベースのテーブルには、データの一意性やリレーションを保つために、よく主キーや外部キーが設定されます。

特に主キーは、大規模になればなるほど間違いなく存在しているキーなのでしっかり学習をしていきましょう。

主キーの役割と設定

主キー（Primary Key）

主キーは、テーブル内の各行を一意に識別するためのカラムまたはカラムの組み合わせのことを指します。

英語ではPrimary keyと呼ばれ、現場ではよく略されて「PK」と呼ばれることが多いです。

また、主キーには以下の特徴があります。

• 一意性：各行が一意に識別される。(すでに同じ項目がDBに存在している状態で、同じ値を入れると、一意性約違反というエラーが起こる)

• 非NULL：主キーの値はNULLを許容しない。(空の値を入れると、エラーが起きます)

また、一意性を保つ大きな理由としては、こと銀行系においては顧客情報などを判別する(誰がどの情報を持っているのか)ために、各顧客にidや顧客番号を割り振り、項目をPKに指定したりすることが多いです。

主キーの設定例：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

この例では、user_idカラムが主キーとして設定されています。

外部キーによるテーブル間のリレーション

外部キー（Foreign Key）

外部キーは他のテーブルの主キーを参照するカラムです。

これにより、テーブル間のリレーション（関係）を表現します。

外部キーの設定には以下のような特徴があります。

• 参照整合性：外部キーは、参照先のテーブルに存在する主キーの値を持たなければならない。

• カスケード操作：参照先のデータが更新・削除された場合に、対応する外部キーも自動的に更新・削除される設定が可能。

そもそも外部キーはどのような場面で使われるの？

外部キーを軽く説明されてもいまいちピンとは来ないと思われます。

ここでは弊社が得意としている金融システムを例に紹介していきます。

金融システムでは、さまざまなエンティティ（顧客、口座、取引など）が存在し、これらが互いに関連しています。

まず、以下は、顧客と取引を管理するためのテーブル設計例です。

顧客テーブル
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

取引テーブル
CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    customer_id INT,
    transaction_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

この設計では、transactionsテーブルのcustomer_idカラムがcustomersテーブルのcustomer_idカラムを参照する外部キーとして設定されています。
このテーブルたちから、外部キーを使用する理由を以下で説明していきます。

1. データの整合性の確保

顧客が存在しない取引が記録されることを防ぎます。

例えば、transactionsテーブルに新しい取引を挿入する際、customer_idは必ずcustomersテーブルに存在する顧客IDでなければなりません。

2. リレーションシップの管理

顧客とその取引を簡単に結び付けることができます。例えば、特定の顧客のすべての取引を取得するクエリは、外部キーにより簡単に実現できます。

SELECT * FROM transactions WHERE customer_id = 1;

3. 参照整合性の維持

顧客が削除された場合、その顧客に関連する取引も削除されるように設定することで、データの整合性を保つことができます。

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    customer_id INT,
    transaction_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
);

3の例では、customersテーブルの顧客が削除されると、それに関連するすべての取引も自動的に削除されます。

外部キーを使用することで、データベース内のデータの一貫性と整合性を確保し、データベース全体の信頼性と効率性を向上させることができます。

金融システムのような複雑なデータ構造を持つシステムでは特に重要になってきます。

まとめ

今回はSQLの基本的な概念であるデータ型、主キー、外部キーについて学びました。

これらの知識を有すると、データベースの設計と管理がより効率的になります。

これらの概念を理解し適切に適用することで、金融システムのデータベース設計は信頼性と効率性を大幅に向上させることができます。

データの整合性を保ちながら、複雑なリレーションシップを管理し、システム全体の安定性と信頼性を高めることができます。