SQL - ユニークインデックス：データ整合性の鍵

こんにちは、データベース愛好家の皆さん！今日は、SQLのユニークインデックスの世界に興味深く飛び込んでみましょう。あなたの近所の親切なコンピュータサイエンスの先生として、私はこのテーマについて過去15年間の教室での情熱を持ってお手伝いします。では、仮想のメモ pad を準備して、一緒に深く掘り下げましょう！

SQL ユニークインデックスとは？

巨大な図書館を整理しているとしましょう。あなたは、2冊の本が同じISBN番号を持つことを防ぎたいと思います。これがデータベース内でユニークインデックスが行うことです。特定の列または複数の列の値がテーブルの2行に同じになることを防ぎます。

ユニークインデックスの基本

SQLにおけるユニークインデックスは、テーブルの1つまたは複数の列の値のユニーク性を強制する特別なインデックスです。これが、あなたのペットにユニークな名前を与えることと同じです。2匹が同じ名前を持つことはできません！

簡単な例から始めましょう：

CREATE TABLE students (
student_id INT PRIMARY KEY,
email VARCHAR(100),
name VARCHAR(50)
);

CREATE UNIQUE INDEX idx_student_email
ON students (email);

この例では、studentsというテーブルを作成し、email列にユニークインデックスを追加しています。これにより、2人の学生が同じメールアドレスを持つことができなくなります。

ユニークインデックスの利用理由

1. データ整合性: 重複データがテーブルに挿入されるのを防ぎます。
2. パフォーマンス: 特定のユニークな値を検索するクエリの速度を向上させることができます。
3. 制約の代替: 一部のケースでは、ユニーク制約の代わりに使用できます。

ユニークインデックスの作成

ユニークインデックスを作成する方法はいくつかあります。それらを見てみましょう！

メソッド1：テーブル作成時

テーブルを初めて作成する際にユニークインデックスを作成することができます：

CREATE TABLE products (
product_id INT PRIMARY KEY,
product_code VARCHAR(20) UNIQUE,
product_name VARCHAR(100)
);

ここで、product_codeは自動的にユニークインデックスが作成されます。

メソッド2：テーブル作成後

既存のテーブルにユニークインデックスを追加することもできます：

CREATE UNIQUE INDEX idx_product_name
ON products (product_name);

これは、productsテーブルのproduct_name列にユニークインデックスを作成します。

重複値での更新

では、ユニークインデックスに既に存在する値をinsertまたはupdateしようとした場合、どうなるのでしょうか？調べてみましょう！

INSERT INTO products (product_id, product_code, product_name)
VALUES (1, 'ABC123', 'Super Widget');

INSERT INTO products (product_id, product_code, product_name)
VALUES (2, 'ABC123', 'Mega Gadget');

最初のinsertは問題ありませんが、2つ目のinsertは失敗します。なぜなら、'ABC123'はすでにproduct_codeとして使用されているからです。SQLはエラーをスローし、データ整合性を保護します。

複数のフィールドに対するユニークインデックス

時々、複数の列に対するユニーク性が必要です。そこで便利なのが複数列のユニークインデックスです！

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
product_id INT
);

CREATE UNIQUE INDEX idx_customer_date_product
ON orders (customer_id, order_date, product_id);

このインデックスにより、同じ顧客が同じ商品を同じ日に2回注文できないようにします。これは、友達が同じ映画の上映を偶然に2枚のチケットを買わないようにするのと同じです！

実世界の例

学校管理システムを構築するとしましょう。学期内で同じコースに同じ学生が複数回登録できないようにしたいと思います：

CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
semester VARCHAR(20),
grade CHAR(1)
);

CREATE UNIQUE INDEX idx_student_course_semester
ON enrollments (student_id, course_id, semester);

これにより、学生がすでにその学期に取っているコースに再登録しようとした場合、SQLは丁寧に断ります：

INSERT INTO enrollments (enrollment_id, student_id, course_id, semester)
VALUES (1, 101, 'CS101', 'Fall 2023');

-- これは成功します

INSERT INTO enrollments (enrollment_id, student_id, course_id, semester)
VALUES (2, 101, 'CS101', 'Fall 2023');

-- これはユニークインデックスのために失敗します

ユニークインデックスメソッド

以下の表に、今まで話したユニークインデックスを作成する方法をまとめます：

メソッド	説明	例
テーブル作成	テーブルを作成する際にユニーク列を定義	CREATE TABLE students (id INT PRIMARY KEY, email VARCHAR(100) UNIQUE);
テーブル変更	既存のテーブルにユニーク制約を追加	ALTER TABLE students ADD CONSTRAINT uc_email UNIQUE (email);
インデックス作成	既存のテーブル(s)に別のユニークインデックスを作成	CREATE UNIQUE INDEX idx_email ON students (email);

結論

そして、ここまでです、私の親愛なる学生たち！私たちはSQLのユニークインデックスの地を旅しました。基本的な概念から複数フィールドでの作成まで、さまざまなことを学びました。ユニークインデックスは、データベースがきれいで整理された状態を保つための強力なツールです。

SQLの冒険を続ける中で、ユニークインデックスは強力なツールですが、賢く使用しましょう。インデックスが多すぎると、データベースのinsertやupdateが遅くなることがあります。完璧なバランスを見つけることが、夜遅くのコーディングセッション中のコーヒーとコードの比率を完璧にするのと同じです！

さあ、ユニークで素晴らしいデータベースを作成しましょう！そして、SQLの世界では、ユニークであることは常にスタイルです。ハッピーコーディング！

Credits: Image by storyset