2021年3月7日日曜日

SQLite 3 既存のカラムから外部キー制約を削除する

概要

SQLite 3 では OracleやMySQLのように「ALTER TABLE～」で外部キー制約を削除することができない。 (ver 3.34.1 時点)
そのため少し面倒な手順をとる必要がある。

詳細

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

基本的には 既存のカラムに外部キー制約を設定する方法 と同様の流れになる。

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

  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」から外部キー制約を削除する例。

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
  • SQLite Command-Line Shell ver 3.34.1
実行手順
  1. トランザクションを開始
  2. スキーマのバージョンを確認
  3. スキーマを編集可能にする
  4. 現在のスキーマを確認する
  5. スキーマテーブルを更新してproduct_idから外部キー制約を削除する。
    スキーマ文は「sqlite_schema」というテーブルに登録されているため、 これをupdateしてやればいい。
  6. 「.schema」コマンドでスキーマが変更されたことを確認する
  7. スキーマのバージョンを一つ上げる
  8. スキーマを編集不能に戻す
  9. データベースが壊れていないかチェックする
  10. トランザクションをコミットする

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 references product(product_id)
    , quantity    text
);

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

sqlite> --# 6.
sqlite> .schema receipt
create table receipt (
    row_num       number
    , create_date text
    , product_id  number
    , 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;

参考URL

Posted by at
Labels: , ,