SQLite 備忘録: SQLite3 カラムの型を変更する

ALTER TABLEのまとめ

概要

SQLiteではOracleやMySQLのように「alter table ～ modify ～」でカラム(列)の構成を変更することが出来ない。 (ver 3.34.0時点)。そのため面倒な手順をとる必要がある。

環境

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

簡単な実行例

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

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

                    
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;

ちゃんとした手順

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

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

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

以下は「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

2021年1月3日日曜日

SQLite3 カラムの型を変更する

概要

環境

簡単な実行例

ちゃんとした手順

参考URL