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

実行環境

• 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

• SQLite Foreign Key Support
  SQLite 公式の外部キーに関するドキュメント(英語)
  https://www.sqlite.org/foreignkeys.html
• ALTER TABLE
  SQLite 公式のテーブル変更に関するドキュメント(英語)
  https://www.sqlite.org/lang_altertable.html

• あさはか備忘録: SQLite 3 外部キー制約のあるテーブルを作成する
  https://sfnovicenotes.blogspot.com/2020/01/sqlite3.html
• あさはか備忘録: SQLite 3 テーブルのスキーマ文を編集する
  https://sfnovicenotes.blogspot.com/2021/01/sqlite-3.html
• あさはか備忘録: SQLite 3 既存のカラムに外部キー制約を設定する
  https://sfnovicenotes.blogspot.com/2021/02/sqlite-3_21.html

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)
                    

実行環境

• 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

• SQLite Foreign Key Support
  SQLite 公式の外部キーに関するドキュメント(英語)
  https://www.sqlite.org/foreignkeys.html
• ALTER TABLE
  SQLite 公式のテーブル変更に関するドキュメント(英語)
  https://www.sqlite.org/lang_altertable.html

• あさはか備忘録: SQLite 3 外部キー制約のあるテーブルを作成する
  https://sfnovicenotes.blogspot.com/2020/01/sqlite3.html
• あさはか備忘録: SQLite 3 既存のカラムから外部キー制約を削除する
  https://sfnovicenotes.blogspot.com/2021/03/sqlite-3.html
• あさはか備忘録: SQLite 3 テーブルのスキーマ文を編集する
  https://sfnovicenotes.blogspot.com/2021/01/sqlite-3.html

SQLite3 新しいカラムを追加して外部キー制約を設定する

概要

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

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

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

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

実行例

「receipt」テーブルに「product_id」を追加し、 外部キー制約を設定する。

実行環境

• Windows 10 64bit
• SQLite3 (3.34.1) Command-Line Shell

カラムの新規追加と外部キー制約の設定は「alter table～」構文を使って まとめて処理できる。

alter table
    receipt
add column 
    product_id number references product(product_id);                    
                    

実際の作業は以下のようになる。

1. トランザクションを開始
2. 既存テーブルのスキーマを確認
3. 外部キー制約を無効化
4. カラムを追加して外部キー制約を設定
5. 設定後のスキーマを確認する
6. 外部キー制約を有効化
7. トランザクションのコミット

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

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

sqlite> -- # 3.
sqlite> pragma foreign_keys=off;

sqlite> -- # 4.
sqlite> alter table
   ...>     receipt
   ...> add column
   ...>     product_id number references product(product_id);

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

sqlite> -- # 6.
sqlite> pragma foreign_keys=on;

sqlite> -- # 7.
sqlite> commit;
                    

「.schema」コマンドの結果が崩れてしまって気になるという場合は以下を参照のこと。
SQLite 3 テーブルのスキーマ文を編集する

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

カラムの追加時にdefault制約を追加出来るため、 指定すればデフォルト値が、指定しなければnullが入る。
ただし、外部キー制約が有効(pragma foreign_keys=on)の状態では デフォルト値をnull以外にすることはできない。

カラム追加時にデフォルト値を指定する場合は以下の通り。

sqlite> .mode table
sqlite> select * from receipt;
+---------+-------------+----------+
| row_num | create_date | quantity |
+---------+-------------+----------+
| 101     | 2019-12-01  | 5        |
| 102     | 2019-12-02  | 5        |
| 103     | 2019-12-03  | 8        |
+---------+-------------+----------+
sqlite> alter table
   ...>     receipt
   ...> add column
   ...>     product_id number references product(product_id) default 0;
sqlite> select * from receipt;
+---------+-------------+----------+------------+
| row_num | create_date | quantity | product_id |
+---------+-------------+----------+------------+
| 101     | 2019-12-01  | 5        | 0          |
| 102     | 2019-12-02  | 5        | 0          |
| 103     | 2019-12-03  | 8        | 0          |
+---------+-------------+----------+------------+
                    

デフォルト値を指定しなかった場合は以下の通り。

sqlite> .mode table
sqlite> .nullval (null)
sqlite> begin transaction;
sqlite> select * from receipt;
+---------+-------------+----------+
| row_num | create_date | quantity |
+---------+-------------+----------+
| 101     | 2019-12-01  | 5        |
| 102     | 2019-12-02  | 5        |
| 103     | 2019-12-03  | 8        |
+---------+-------------+----------+
sqlite> alter table
   ...>     receipt
   ...> add column
   ...>     product_id number references product(product_id);
sqlite> select * from receipt;
+---------+-------------+----------+------------+
| row_num | create_date | quantity | product_id |
+---------+-------------+----------+------------+
| 101     | 2019-12-01  | 5        | (null)     |
| 102     | 2019-12-02  | 5        | (null)     |
| 103     | 2019-12-03  | 8        | (null)     |
+---------+-------------+----------+------------+
                    

pragma foreign_keys=on のまま、カラムを追加して外部キーと null以外のデフォルト値を設定しようするとエラーになる。

sqlite> pragma foreign_keys=on;
sqlite> alter table
   ...>     receipt
   ...> add column
   ...>     product_id number references product(product_id) default 0;
Error: Cannot add a REFERENCES column with non-NULL default value
                    

注意

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

参考URL

• ALTER TABLE ADD COLUMN
  公式のalter table に関するドキュメント
  https://www.sqlite.org/lang_altertable.html#alter_table_add_column
• SQLite Foreign Key Support
  SQLite 公式の外部キーに関するドキュメント(英語)
  https://www.sqlite.org/foreignkeys.html

• あさはか備忘録: SQLite 3 新しいカラム(列)を追加する
  https://sfnovicenotes.blogspot.com/2020/12/sqlite3_8.html
• あさはか備忘録: SQLite3 外部キー制約のあるテーブルを作成する
  https://sfnovicenotes.blogspot.com/2020/01/sqlite3.html

SQLite3 外部キー制約のあるテーブルを作成する

概要

外部キー制約のあるテーブルを作成する方法。
SQLite ver. 3.6.19 からテーブルに外部キー制約が設定できるようになっている。
ただし、毎回(接続ごと)外部キー制約の設定を有効にする必要がある。
(オプションを指定してバイナリをコンパイルすれば、デフォルトで外部キー制約を有効にできるらしい)

注意

親キーとなる項目は 主キーかユニークキーを設定し、indexを作成する。
複合キーでもいい。

外部キー制約の有効/無効を切り替える

環境

• Windows 10
• SQLite Command-Line Shell ver 3.29.0

sqlite> -- # 1. 
sqlite> PRAGMA foreign_keys;
0
sqlite> -- # 2. 
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> -- # 3.
sqlite> PRAGMA foreign_keys = OFF;
sqlite> PRAGMA foreign_keys;
0
                    

1. 現在の外部キー制約が有効か無効かを確認する
   デフォルトは無効。
2. 外部キー制約を有効にする
3. 外部キー制約を無効にする

サンプルテーブル

以下のようなデータを想定してテーブルを作成する。
「レシート(receipt)」の「product_id」は 「商品(product)」の「product_id」を外部キーとする。

商品 (product)

product_id	product_name	price
1	tomato	100
2	potato	80
3	pumpkin	120

レシート (receipt)

row_num	create_date	product_id	quantity
101	2019-12-01	1	5
102	2019-12-02	2	5
103	2019-12-03	1	8
104	2019-12-05	2	3
105	2019-12-08	3	2

外部キー制約のあるテーブルを作成する

PRAGMA foreign_keys = ON;
create table if not exists 'product' (
    product_id     integer primary key autoincrement
    , product_name text    not null
    , price        integer default 0
);
create table if not exists 'receipt' (
    rownum         integer primary key autoincrement
    , create_date  text    default current_timestamp
    , product_id   integer not null
    , quantity     integer default 0
    , foreign key  (product_id) references product(product_id)
);
                    

複合キーのサンプルテーブル

複合キーを外部キーとして指定する場合のサンプル。
以下のようなデータを想定してテーブルを作成する。
「レシート(receipt)」の「product_name」と「producer」は 「商品(product)」の「product_id」と「producer」を外部キーとする。

商品 (product)

product_name	producer	price
tomato	farmerA	100
potato	farmerA	80
potato	farmerB	85
pumpkin	farmerC	120

レシート (receipt)

row_num	create_date	product_name	product_farmer	quantity
101	2019-12-01	potato	farmerB	5
102	2019-12-02	tomato	farmerA	5
103	2019-12-03	pumpkin	farmerC	8
104	2019-12-05	potato	farmerB	3
105	2019-12-08	tomato	farmerA	2

複合外部キー制約のあるテーブルを作成する

PRAGMA foreign_keys = ON;
create table if not exists "product" (
    product_name   text    not null
    , producer     text    not null
    , price        integer default 0
    , primary key (product_name, producer)
);
create table if not exists "receipt" (
    rownum         integer primary key autoincrement
    , create_date  text default current_timestamp
    , product_name text not null
    , producer     text not null
    , quantity     integer default 0
    , foreign key (product_name, producer) references product(product_name, producer)
);
                    

参考URL

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite 公式の外部キーに関するドキュメント(英語)
  https://www.sqlite.org/foreignkeys.html

• SQLite 3 既存のカラムに外部キー制約を設定する
  https://sfnovicenotes.blogspot.com/2021/02/sqlite-3_21.html
• SQLite 3 既存のカラムから外部キー制約を削除する
  https://sfnovicenotes.blogspot.com/2021/03/sqlite-3.html