SQLiteでPRIMARY KEYとUNIQUE制約を組み合わせる際の注意点とは？

2024-05-21

SQLiteで列を PRIMARY KEY + UNIQUE と宣言した場合

主キー (PRIMARY KEY)

その列の値は、テーブル内のすべての行で一意でなければなりません。つまり、同じ値を持つ行は存在できません。
その列の値は NULL であることができません。
テーブルに複数の主キー列を設定することはできません。

PRIMARY KEY と UNIQUE の違い

PRIMARY KEY は、テーブルに 1つだけ 設定できる制約です。一方、UNIQUE は、テーブルに複数設定できる制約です。
PRIMARY KEY は、その列の値が NULL であることができません。一方、UNIQUE は、その列の値が NULL であることを許可しています。

PRIMARY KEY と UNIQUE を併用すると、その列は主キーであり、かつユニーク制約にもなります。つまり、その列の値は、テーブル内のすべての行で一意でなければならず、NULL であることもできません。

例

CREATE TABLE users (
  id INTEGER PRIMARY KEY UNIQUE,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

上記の例では、users テーブルの id 列は主キーであり、かつユニーク制約にもなります。つまり、すべてのユーザーの id は異なる値であり、NULL であることもできません。また、email 列はユニーク制約のみ設定されています。つまり、すべてのユーザーの email は異なる値である必要がありますが、NULL であることも許可されています。

CREATE TABLE users (
  id INTEGER PRIMARY KEY UNIQUE,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

このコードを実行すると、以下のusers テーブルが作成されます。

id | name       | email
----+------------+---------
1  | Alice     | [email protected]
2  | Bob       | [email protected]
3  | Charlie   | [email protected]

このテーブルの id 列は主キーであり、かつユニーク制約にもなっているので、以下の操作は許可されません。

同じ id 値を持つ行を挿入する
id 列の値を NULL に設定する

一方、以下の操作は許可されます。

id 列の値を変更する

例：レコードの挿入

以下のコードは、users テーブルに新しいレコードを挿入する例です。

INSERT INTO users (name, email)
VALUES ('David', '[email protected]');

id | name       | email
----+------------+---------
1  | Alice     | [email protected]
2  | Bob       | [email protected]
3  | Charlie   | [email protected]
4  | David     | [email protected]

以下のコードは、users テーブルの id が 2 であるレコードの name 列を 'Bob Smith' に更新する例です。

UPDATE users
SET name = 'Bob Smith'
WHERE id = 2;

id | name       | email
----+------------+---------
1  | Alice     | [email protected]
2  | Bob Smith  | [email protected]
3  | Charlie   | [email protected]
4  | David     | [email protected]

DELETE FROM users
WHERE id = 3;

id | name       | email
----+------------+---------
1  | Alice     | [email protected]
2  | Bob Smith  | [email protected]
4  | David     | [email protected]

このサンプルコードは、PRIMARY KEY と UNIQUE 制約の使用方法を理解するのに役立ちます。

PRIMARY KEY と UNIQUE 制約の代替方法

チェック制約を使用して、列の値が特定の条件を満たしていることを確認できます。例えば、以下のチェック制約は、email 列の値が有効なメールアドレスであることを確認します。

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE CHECK (email LIKE '%@%'),
);

トリガーを使用して、行が挿入または更新されるたびに特定のアクションを実行できます。例えば、以下のトリガーは、email 列の値が変更されるたびに、その変更を監査ログテーブルに記録します。

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

CREATE TRIGGER audit_email_changes
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (user_id, old_email, new_email)
  VALUES (NEW.id, OLD.email, NEW.email);
END;

カスタムロジック

アプリケーションロジックを使用して、列の値が一意であることを確認することもできます。これは、複雑な一意性要件がある場合に役立ちます。

単純な一意性要件 の場合は、UNIQUE 制約を使用するのが最も簡単です。
より複雑な一意性要件 の場合は、チェック制約、トリガー、またはカスタムロジックを使用する必要があります。
パフォーマンスが重要 な場合は、UNIQUE 制約を使用するのが最善です。チェック制約とトリガーは、パフォーマンスに悪影響を与える可能性があります。
監査が必要 な場合は、トリガーを使用するのが最善です。

SQLite で列の値が一意であることを保証するには、さまざまな方法があります。適切な方法は、要件によって異なります。

sqlite