2021年2月21日日曜日

SQLite 3 既存のカラムに外部キー制約を設定する

概要

SQLiteでは既存のテーブルに外部キー制約を追加するのが少し面倒。 いくつか方法があるので、内容によって使い分けるといい。

既存テーブルに外部キーを設定するパターンは 大まかには以下の3つ。

  1. カラムを新規追加して外部キーを設定する。
  2. 既存のカラムに外部キーを設定する。
  3. 既存のカラムのデータ型を変更して外部キーを設定する。

ここでは2.の実行例を記載する。

詳細

OracleやMySQLであれば「alter table ～ add foreign key ～」のように alter table 構文で既存テーブルに外部キー制約を追加することができるが、 SQLiteではこれができない。(ver 3.34.1時点)

SQLiteのテーブルの構成を変更する方法として、 テーブルを作り直してデータを移行する方法 があるが、既存のカラムに外部キーを設定するだけであれば もう少し簡単な方法がある。

簡単に説明すると SQLiteではテーブル構成がスキーマ文(DDL)で保存されており、中身のデータとは別々に管理されている。 データ型を変更するような場合はスキーマ文とデータの両方に手を加える必要があるが、 外部キー制約の追加だけであればスキーマ文のみを変更するだけでいい、ということらしい。

公式(英語) に手順が記載されている。

  1. トランザクションを開始。
  2. Pragma schema_versionでスキーマのバージョンを確認する。
  3. Pragma writable_schema=onにする。
  4. sqlite_schemaテーブルに保存されているスキーマ文を更新する。
  5. 他のテーブルに影響が出る場合はそちらにも変更を加える。
    (外部キー制約を追加するだけであれば不要。)
  6. Pragma schema_version=Xでスキーマのバージョンを+1する。
  7. Pragma writable_schema=offにする。
  8. Pragma Integrity_checkを実行してデータベースが壊れていないか確認する。
  9. トランザクションをコミットする。

実行例

以下は「receipt」テーブルの「product_id」に外部キー制約を設定する例。
外部キー制約を設定するにはカラムの後に、referencesと参照するテーブル・カラムを追加する。

以下の構文を追記する

references table_name(columu_name)
image/svg+xml product (商品) product (商品) PK product_id product_name price receipt(レシート) receipt(レシート) row_num create_date FK product_id quantity Viewer does not support full SVG 1.1
実行環境
  • Windows 10 64bit
  • SQLite3 (3.34.1) Command-Line Shell
実行手順
  1. トランザクションを開始
  2. スキーマのバージョンを確認
  3. スキーマを編集可能にする
  4. 現在のスキーマを確認する
  5. スキーマテーブルを更新してproduct_idに外部キー制約を追加する。
    スキーマ文は「sqlite_schema」というテーブルに登録されているため、 これをupdateしてやればいい。
  6. 「.schema」コマンドでスキーマが変更されたことを確認する
  7. スキーマのバージョンを一つ上げる
  8. スキーマを編集不能に戻す
  9. データベースが壊れていないかチェックする
  10. トランザクションをコミットする
  11. 正しく外部キー制約が追加されたか確認する

sqlite> CREATE TABLE product (
   ...>     product_id     number primary key
   ...>     , product_name text
   ...>     , price        number
   ...> );
sqlite> CREATE TABLE receipt (
   ...>     row_num       number
   ...>     , create_date text
   ...>     , product_id  number
   ...>     , quantity    text
   ...> );

sqlite> --# 1.
sqlite> begin transaction;

sqlite> --# 2.
sqlite> pragma schema_version;
2

sqlite> --# 3.
sqlite> pragma writable_schema=on;

sqlite> --# 4.
sqlite> .schema receipt
CREATE TABLE receipt (
    row_num       number
    , create_date text
    , product_id  number
    , quantity    text
);

sqlite> --# 5.
sqlite> update
   ...>     sqlite_schema
   ...> set
   ...>     sql=
   ...> 'create table receipt (
   ...>     row_num       number
   ...>     , create_date text
   ...>     , product_id  number references product(product_id)
   ...>     , quantity    text
   ...> )'
   ...> where
   ...>     tbl_name = 'receipt'
   ...> ;

sqlite> --# 6.
sqlite> .schema receipt
create table receipt (
    row_num       number
    , create_date text
    , product_id  number references product(product_id)
    , quantity    text
);

sqlite> --# 7.
sqlite> pragma schema_version=3;

sqlite> --# 8.
sqlite> pragma writable_schema=off;

sqlite> --# 9.
sqlite> pragma Integrity_check;
ok

sqlite> --# 10.
sqlite> commit;

sqlite> --# 11.
sqlite> pragma foreign_keys=on;
sqlite> insert into receipt values (
   ...>     5
   ...>     , '2021-02-21'
   ...>     , 99999
   ...>     , 100
   ...> );
Error: FOREIGN KEY constraint failed

既存のデータはどうなるのか

既存のデータが外部キー制約違反になっていてもエラーにはならない。

きちんとやるなら「Pragma foreign_keys=on」で外部キー制約を有効ににした後、 外部キー制約違反にならないようデータを修正する。
どのデータが外部キー制約違反かわからない場合は「pragma foreign_key_check」で確認できる。

参考URL

Posted by at
Labels: , ,