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