SQLite3 カラムの型を変更する

概要

SQLiteではOracleやMySQLのように ALTER TABLE ～ MODIFY ～でカラム（列）の構成を変更することができない。 (ver 3.34.0時点)。 そのため面倒な手順をとる必要がある。

環境

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

簡単な実行例

以下はシンプルなパターンでの実行例。
「product」テーブルの「price」を integer 型からtext型に変更する。

1. カラムを変更した状態のテーブルを別名で作成する。
2. 元のテーブルから新規作成したテーブルへデータをコピーする。
3. 元のテーブルを削除する。
4. 新規作成したテーブルの名前を変更する。

• SQLite3 Selectしたデータを他のテーブルに挿入
• SQLite3 テーブル名を変更する (RENAME)

                    
sqlite> .schema product
CREATE TABLE product (
    id       integer
    , name   text
    , price  integer
    , remark text
);

sqlite> -- # 1.
sqlite> CREATE TABLE new_product (
   ...>     id       integer
   ...>     , name   text
   ...>     , price  text
   ...>     , remark text
   ...> );

sqlite> -- # 2.
sqlite> INSERT INTO
   ...>     new_product
   ...> SELECT
   ...>     *
   ...> FROM
   ...>     product
   ...> ;

sqlite> -- # 3.
sqlite> DROP TABLE product;

sqlite> -- # 4.
sqlite> ALTER TABLE new_product RENAME TO product;
                    
                

ちゃんとした手順

上記はシンプルな例だが、実際は外部キーやトリガーなどの絡みがあるため複雑になる。
万全を期すなら公式に適切な手順が記載してあるので、それに倣う。

テーブルのスキーマを変更するので念のためバックアップは取っておくこと。

1. 外部キー制約を無効化する
2. トランザクションを開始する
3. 対象のテーブルを参照しているインデックス、トリガー、ビューを確認する
4. カラムを変更した構成の新しいテーブルを作成する
5. 対象のテーブルから新規作成したテーブルへデータをコピーする
6. 対象のテーブルを参照しているビューを削除する
7. 古いほうのテーブルを削除する
8. 新規作成したテーブルの名前を元のテーブル名に変更する
9. 対象のテーブルを参照していたインデックス、トリガー、ビューを再作成する
10. 外部キー制約が破綻していないかPRAGMA foreign key checkで確認する
11. トランザクションをコミットする
12. 外部キー制約を有効化する

以下は「product」テーブルの「price」を integer 型から text 型に変更する手順。 「product」テーブルはインデックスとトリガーが作られており、 「v_total」というビューから参照されている。

sqlite> -- # 1.
sqlite> PRAGMA foreign_key=off;

sqlite> -- # 2.
sqlite> BEGIN TRANSACTION;

sqlite> -- # 3.
sqlite> SELECT type, sql FROM sqlite_schema WHERE tbl_name='product';
table|CREATE TABLE product (
    id       integer primary key
    , name   text    not null
    , price  integer default 0
    , remark text
)
index|CREATE INDEX idx_product on product (name)
trigger|CREATE TRIGGER tg_inserted_product
BEFORE INSERT ON
    product
BEGIN
    INSERT INTO
        product_log (
            log_date
            , detail
        )
        VALUES (
            date('now')
            , 'new data inserted'
        )
    ;
end

sqlite> -- # 4. 
sqlite> CREATE TABLE
   ...>     new_product (
   ...>         id       integer primary key
   ...>         , name   text
   ...>         , price  text
   ...>         , remark text
   ...>     )
   ...> ;

sqlite> -- # 5.
sqlite> INSERT INTO
   ...>     new_product
   ...> SELECT
   ...>     *
   ...> FROM
   ...>     product
   ...> ;

sqlite> -- # 6.
sqlite> DROP VIEW v_total;

sqlite> -- # 7.
sqlite> DROP TABLE product;

sqlite> -- # 8.
sqlite> ALTER TABLE new_product RENAME TO product;

sqlite> -- # 9.
sqlite> CREATE INDEX
   ...>     idx_product
   ...> ON
   ...>     product(name)
   ...> ;
sqlite> CREATE TRIGGER
   ...>     tg_inserted_product
   ...> BEFORE INSERT ON
   ...>     product
   ...> BEGIN
   ...>     INSERT INTO
   ...>         product_log (
   ...>             log_date
   ...>             , detail
   ...>         )
   ...>         VALUES (
   ...>             date('now')
   ...>             , 'new data inserted'
   ...>         )
   ...>     ;
   ...> END;
sqlite> CREATE VIEW
   ...>     v_total
   ...> AS
   ...> SELECT
   ...>     sum(price)
   ...> FROM
   ...>     product
   ...> ;

sqlite> -- # 10.
sqlite> PRAGMA foreign_key_check;

sqlite> -- # 11.
sqlite> COMMIT;

sqlite> -- # 12.
sqlite> PRAGMA foreign_key=on;
                

テーブル名を変更する段階で古いテーブルを参照していたビューが残っていると以下のようなエラーになる。

Error: error in view v_remarks: no such table: main.product
                

ver 3.34.1 まではカラムの削除も同様の手順をとる必要があった。
SQLite 3 カラムを削除する (～ver 3.34.1)
SQLite 3 ではテーブルに対してカラムの変更や削除ができない

ver 3.35.0 以降ではALTER TABLE ～ DROP COLUMNがサポートされている
SQLite 3 カラムを削除する

参考URL

• Making Other Kinds Of Table Schema Changes
  公式のテーブルスキーマ変更に関するドキュメント
  https://sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes