SQLite 備忘録: SQLite 3 カラムを削除する (～ver 3.34.1)

ALTER TABLEのまとめ

2021.4.11 追記

バージョン 3.35.0 で「alter table ～ drop column」構文がサポートされた。より簡単にカラムを削除出来るようになっている。

詳しくは以下のURLより。

SQLite 3 カラムを削除する
ALTER TABLE DROP COLUMN (公式のカラム削除に関する情報)

以下はver 3.34.1以下で作業したい場合のコマンド例

概要

SQLiteのver 3.34.1以下では「alter table ～ drop column」ではカラム(列)を削除することができない。そのため少々面倒な手順をとる必要がある。

カラムの変更も削除と同じような手順をとる必要がある。
SQLite3 カラムの型を変更する
SQLite3 ではテーブルに対してカラムの変更や削除ができない

環境

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

簡単な実行例

以下はシンプルなパターンでの実行例。
「product」テーブルから「remark」カラムを削除する。

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


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 integer
   ...> );

sqlite> -- # 2.
sqlite> insert into new_product (
   ...>    id
   ...>    , name
   ...>    , price
   ...> )
   ...> select
   ...>    id
   ...>    , name
   ...>    , price
   ...> from
   ...>    product
   ...> ;

sqlite> -- # 3.
sqlite> drop table product;

sqlite> -- # 4.
sqlite> alter table new_product rename to product;

ちゃんとした手順

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

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

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

以下は「product」というテーブルから「remark」というカラムを消す手順。「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 integer default 0
   ...>     )
   ...> ;

sqlite> -- # 5.
sqlite> insert into
   ...>     new_product (
   ...>         id
   ...>         , name
   ...>         , price
   ...>     )
   ...> select
   ...>     id
   ...>     , name
   ...>     , price
   ...> 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_total: no such table: main.product

参考URL

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

2020年12月20日日曜日

SQLite 3 カラムを削除する (～ver 3.34.1)

2021.4.11 追記

概要

環境

簡単な実行例

ちゃんとした手順

参考URL