SQLite 複合主キーとFOREIGN KEY制約の連携

SQLiteで複数の列に主キーを設定する方法

設定方法

SQLiteで複数の列に主キーを設定するには、CREATE TABLE ステートメントで PRIMARY KEY 制約を指定します。具体的には、以下の2つの方法があります。

列名をカンマで区切る

CREATE TABLE テーブル名 (
  列名1 型,
  列名2 型,
  ...
  PRIMARY KEY (列名1, 列名2, ...)
);

PRIMARY KEY 制約を複数回指定する

CREATE TABLE テーブル名 (
  列名1 型,
  列名2 型,
  ...
  PRIMARY KEY (列名1),
  PRIMARY KEY (列名2),
  ...
);

どちらの方法でも、複合主キーとして指定された列の組み合わせは、テーブル内のすべての行で一意である必要があります。

例

例えば、社員 テーブルを作成する際に、社員ID と 氏名 を組み合わせて主キーとして設定したい場合は、以下のいずれかの方法で記述できます。

方法1

CREATE TABLE 社員 (
  社員ID INTEGER PRIMARY KEY,
  氏名 VARCHAR(255) NOT NULL,
  ...
);

CREATE TABLE 社員 (
  社員ID INTEGER,
  氏名 VARCHAR(255) NOT NULL,
  ...
  PRIMARY KEY (社員ID, 氏名)
);

上記の場合、社員ID と 氏名 の組み合わせが重複することは許されません。

複合主キーのメリット

複合主キーを設定する主なメリットは以下の通りです。

• データの重複を防ぐことができる
• 一意なレコード識別子を生成できる
• 外部キー制約との連携が容易になる

複合主キーのデメリット

• インデックスサイズが大きくなる
• クエリ処理が複雑になる場合がある

CREATE TABLE 書籍 (
  書籍ID INTEGER PRIMARY KEY,
  書名 VARCHAR(255) NOT NULL,
  著者 VARCHAR(255) NOT NULL,
  出版社 VARCHAR(255) NOT NULL,
  発行年 INTEGER NOT NULL
);

このテーブルでは、書籍ID を主キーとしています。しかし、書籍ID だけでは一意にレコードを識別できない場合があります。例えば、同じ著者によって書かれた異なる書籍が、同じ 書籍ID を持つ可能性があります。

複合主キーの設定

そこで、書名 と 著者 も主キーに追加することで、書籍を一意に識別できるようにします。

ALTER TABLE 書籍
ADD PRIMARY KEY (書名, 著者);

この変更により、書籍ID と 書名 と 著者 の組み合わせが重複することは許されなくなります。

複合主キーは、以下の例のように、さまざまな操作で使用できます。

• 特定の書籍を検索する

SELECT * FROM 書籍
WHERE 書名 = '書籍名' AND 著者 = '著者名';

• 書籍を更新する

UPDATE 書籍
SET 出版社 = '新しい出版社'
WHERE 書名 = '書籍名' AND 著者 = '著者名';

DELETE FROM 書籍
WHERE 書名 = '書籍名' AND 著者 = '著者名';

複合主キーは、データの重複を防ぎ、一意なレコード識別子を生成するために役立ちます。サンプルコードを参考に、複合主キーを有効活用してください。

複合主キーを設定する他の方法

UNIQUE 制約と REFERENCES 制約の組み合わせ

CREATE TABLE テーブル名 (
  列名1 型,
  列名2 型,
  ...
  UNIQUE (列名1, 列名2, ...),
  FOREIGN KEY (列名1, 列名2, ...) REFERENCES 他のテーブル名 (列名1, 列名2, ...)
);

この方法では、UNIQUE 制約を使用して列の組み合わせの重複を防ぎ、REFERENCES 制約を使用して他のテーブルとの参照関係を定義します。

CHECK 制約

CREATE TABLE テーブル名 (
  列名1 型,
  列名2 型,
  ...
  CHECK (列名1 IS NOT NULL AND 列名2 IS NOT NULL),
  CHECK (列名1 || 列名2 UNIQUE)
);

この方法では、CHECK 制約を使用して、列の組み合わせがNULLではないことと、一意であることを確認します。

仮想列

CREATE TABLE テーブル名 (
  列名1 型,
  列名2 型,
  ...
  仮想列名 AS (列名1 || 列名2),
  PRIMARY KEY (仮想列名)
);

この方法では、VIRTUAL キーワードを使用して、複数の列を結合した仮想列を作成し、その仮想列を主キーとして設定します。

どの方法を選択するべきかは、状況によって異なります。一般的には、以下の点を考慮する必要があります。

• データベースの構造
• データ操作の頻度
• パフォーマンス

SQLiteで複合主キーを設定するには、さまざまな方法があります。それぞれの方法のメリットとデメリットを理解した上で、状況に合った方法を選択することが重要です。