SQLite INTEGER PRIMARY KEY vs UNIQUE制約：違いを理解して使い分ける

SQLiteにおけるINTEGER PRIMARY KEY：詳細解説

この解説では、INTEGER PRIMARY KEYの概念をプログラミング初心者にも分かりやすく掘り下げ、以下の点を詳細に解説します。

• SQLiteにおける「Language」と「INTEGER」の関係
• INTEGER PRIMARY KEYの役割と重要性
• UNIQUE制約との違い
• AUTOINCREMENTオプションの仕組み
• ROWIDとの関連性
• 実際のコード例と設定方法
• INTEGER PRIMARY KEYの使用における注意点
• 代替案と比較

SQLiteにおける「Language」と「INTEGER」

SQLiteは、SQLと呼ばれる構造化クエリ言語を使用してデータベースを操作します。Languageは、SQLiteが使用する言語体系を指します。

INTEGERは、SQLiteで数値データを格納するためのデータ型です。整数値のみを保存でき、小数点以下の桁数は扱えません。

INTEGER PRIMARY KEYの役割と重要性

INTEGER PRIMARY KEYは、テーブル内の各レコードを一意に識別するための主キーです。以下の役割を担います。

• データの重複を防ぐ: 同じ値を持つレコードは複数登録できません。
• レコードへの効率的なアクセス: 主キーに基づいて、特定のレコードを素早く検索できます。
• 外部キーとの関連付け: 別のテーブルとのデータ連携を可能にします。

PRIMARY KEYは、テーブル内に1つのみ設定できます。NULL値を許容せず、必ず一意な値が設定されなければなりません。

UNIQUE制約との違い

UNIQUE制約も、列の値が重複することを防ぐ制約です。しかし、PRIMARY KEYとの違いは以下の通りです。

• NULL値の許容: UNIQUE制約は、列にNULL値を許容できます。
• 主キーとしての役割: UNIQUE制約は、主キーとして機能しません。
• 複数設定可能: テーブル内に複数のUNIQUE制約を設定できます。

UNIQUE制約は、データの重複を防ぐための補助的な手段として使用されます。

AUTOINCREMENTオプションの仕組み

AUTOINCREMENTオプションを指定すると、INTEGER PRIMARY KEYの値が自動的に1ずつ増加していきます。

• 新しいレコードを挿入する際、PRIMARY KEY列に値を指定する必要はありません。
• 自動的に割り当てられた値は、ROWIDと呼ばれる内部的な識別子と一致します。

AUTOINCREMENTオプションは、レコードの挿入を簡略化し、一意性を保証するのに役立ちます。

ROWIDとの関連性

ROWIDは、SQLiteが内部的に使用するレコードの識別番号です。INTEGER PRIMARY KEYを指定すると、ROWIDは自動的にPRIMARY KEYと同じ値になります。

ROWIDは、直接操作することはできませんが、PRIMARY KEYと密接に関連しています。

実際のコード例と設定方法

以下のコード例は、INTEGER PRIMARY KEYとAUTOINCREMENTオプションを使用してテーブルを作成する例です。

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  email TEXT
);

このコードを実行すると、idという名前のINTEGER PRIMARY KEY列と、nameとemailという名前のTEXT列を持つusersというテーブルが作成されます。

AUTOINCREMENTオプションを省略すると、PRIMARY KEYの値を手動で設定する必要があります。

INTEGER PRIMARY KEYの使用における注意点

INTEGER PRIMARY KEYを使用する際には、以下の点に注意が必要です。

• データ型の制限: INTEGER型は、整数値のみを格納できます。小数点以下の桁数は扱えません。
• 連番の欠番: レコードを削除すると、AUTOINCREMENTによって割り当てられる連番に欠番が生じます。
• 外部キーとの連携: 外部キーとの連携を考慮する場合は、INTEGER PRIMARY KEYのデータ型とサイズを慎重に検討する必要があります。

代替案と比較

INTEGER PRIMARY KEYの代替案として、以下の選択肢があります。

• UNIQUEIDENTIFIER: ランダムな値を生成する

SQLite INTEGER PRIMARY KEY サンプルコード集

基本的なテーブル作成

-- テーブル作成
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  email TEXT
);

-- データ挿入
INSERT INTO users (name, email) VALUES ('山田太郎', '[email protected]');
INSERT INTO users (name, email) VALUES ('佐藤花子', '[email protected]');

-- データ検索
SELECT * FROM users;

-- 結果
-- id | name       | email
-- --- | -------- | --------
-- 1  | 山田太郎 | [email protected]
-- 2  | 佐藤花子 | [email protected]

AUTOINCREMENT オプションなし

-- テーブル作成
CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  content TEXT
);

-- データ挿入
INSERT INTO articles (id, title, content) VALUES (1, 'タイトル1', '本文1');
INSERT INTO articles (title, content) VALUES ('タイトル2', '本文2');

-- データ検索
SELECT * FROM articles;

-- 結果
-- id | title       | content
-- --- | -------- | --------
-- 1  | タイトル1 | 本文1
-- 2  | タイトル2 | 本文2

• id 列に値を明示的に指定しています。
• AUTOINCREMENT オプションがない場合は、主キーの値を手動で設定する必要があります。

UNIQUE 制約との比較

-- テーブル作成
CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE,
  price INTEGER
);

-- データ挿入
INSERT INTO products (name, price) VALUES ('商品1', 1000);
INSERT INTO products (name, price) VALUES ('商品1', 2000); -- エラー発生

-- エラーメッセージ
-- UNIQUE constraint failed: products.name

ポイント:

• name 列に UNIQUE 制約を設定しています。
• 同じ名前の商品は登録できないため、2番目の INSERT 文はエラーになります。

外部キーとの連携

-- テーブル作成
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  product_id INTEGER,
  FOREIGN KEY (user_id) REFERENCES users (id),
  FOREIGN KEY (product_id) REFERENCES products (id)
);

-- データ挿入
INSERT INTO orders (user_id, product_id) VALUES (1, 1);
INSERT INTO orders (user_id, product_id) VALUES (2, 2);

-- データ検索
SELECT * FROM orders;

-- 結果
-- id | user_id | product_id
-- --- | -------- | --------
-- 1  | 1       | 1
-- 2  | 2       | 2

ポイント:

• orders テーブルの user_id 列と product_id 列に外部キー制約を設定しています。
• 外部キー制約により、users テーブルと products テーブルとのデータ整合性を保つことができます。

その他のサンプル

複合主キー*

CREATE TABLE addresses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  postal_code TEXT,
  address TEXT,
  FOREIGN KEY (user_id) REFERENCES users (id)
);

CHECK 制約*

CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT,
  author TEXT,
  price INTEGER CHECK (price > 0)
);

このサンプルコード集は、SQLite の INTEGER PRIMARY KEY の様々な使用方法を理解するのに役立ちます。

注意:

• これらのコードはあくまでもサンプルであり、必要に応じて修正する必要があります。
• データベース操作を行う前に、必ずバックアップを取るようにしてください。

INTEGER PRIMARY KEY 以外の代替方法

• データ型: 整数値のみを扱えるため、文字列や日付などの他のデータ型には使用できません。
• 連番の欠番: レコードを削除すると、自動的に割り当てられる連番に欠番が生じます。
• 外部キーとの連携: 外部キーとの連携を考慮する場合は、データ型とサイズを慎重に検討する必要があります。

これらの制限を回避するために、以下の代替方法を検討することができます。

UNIQUEIDENTIFIER 型

UNIQUEIDENTIFIER 型は、ランダムな値を生成するデータ型です。以下の特徴があります。

• 一意性: すべての値がユニークであるため、重複する可能性がありません。
• データ型: 16 バイトのバイナリ値として格納されます。
• 連番の欠番: レコードを削除しても、連番に欠番が生じません。

欠点:

• 読みやすさ: ランダムな値のため、人間にとって読みづらいです。
• 外部キーとの連携: 外部キーとの連携には、変換が必要になる場合があります。

CREATE TABLE users (
  id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (newid()),
  name TEXT,
  email TEXT
);

TEXT 型

TEXT 型は、文字列を格納できるデータ型です。以下の特徴があります。

• データ型: 最大 65,535 文字までの文字列を格納できます。
• 読みやすさ: 人間にとって読みやすい値を設定できます。
• 外部キーとの連携: 外部キーとの連携が容易です。

欠点:

• 一意性: 値が重複する可能性があります。
• パフォーマンス: ソートや検索などのパフォーマンスが低下する可能性があります。

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  name TEXT,
  email TEXT
);

複合主キー**

複数の列を組み合わせた主キーを設定することで、一意性を確保できます。

CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  product_id INTEGER,
  PRIMARY KEY (user_id, product_id)
);

サロゲートキー**

テーブルの論理的な意味を持つ別の列を主キーとして設定します。

CREATE TABLE users (
  username TEXT PRIMARY KEY,
  name TEXT,
  email TEXT
);

最適な方法は、テーブルの設計と要件によって異なります。

• 一意性とパフォーマンスの両方を重視する場合は、UNIQUEIDENTIFIER 型がおすすめです。
• 読みやすさを重視する場合は、TEXT 型がおすすめです。
• 外部キーとの連携を重視する場合は、複合主キー や サロゲートキー がおすすめです。

それぞれの方法の特徴とメリット・デメリットを理解した上で、適切な方法を選択することが重要です。