SQLite3 カラムを削除する (DROP COLUMN)

概要

既存のテーブルのカラムを削除する方法。
OracleやMySQLのように「alter table ～ drop column ～」で削除する。

ver 3.35.0 以降で利用可能。

構文

alter table table_name drop column column_name
                

もしくは

alter table table_name drop column_name
                

複数のカラムを指定することは出来ない。

実行例

以下の「product」テーブルから「remark」カラムを削除する。

「product」テーブル

カラム名	データ型	制約
id	integer	primary key
name	text	not null
quantity	integer	default 0
remark	text

環境

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

sqlite> --# 1.
sqlite> .schema product
CREATE TABLE product (
        id         integer primary key
        , name     text    not null
        , quantity integer default 0
        , remark   text
);

sqlite> --# 2.
sqlite> alter table product drop column remark;
sqlite> .schema product
CREATE TABLE product (
        id         integer primary key
        , name     text    not null
        , quantity integer default 0
        );
                

1. 現在の「product」テーブルのスキーマを確認
2. 「alter table」でカラムを削除、スキーマが変更されていることを確認

上記の.schemaコマンドの結果を見るとわかるが、 カラムを削除した後はスキーマの見た目が崩れてしまう。

崩れてしまったスキーマ文を修正したい場合はこちら
SQLite 3 スキーマ文を編集する

エラーになるカラム

以下の要素を持つカラムは「alter table ～ drop column」では削除出来ない。

• 主キーとなっている
• ユニーク制約が設定されている
• インデックスが作成されている
• 部分インデックスで使用されている
• 他のカラムやテーブルのCHECK制約で使用されている
• 外部キー制約に使用されている
• 自動生成カラムで使用されている
• トリガーやビューで使用されている

上記のカラムをどうしても削除したい場合は、 ver 3.34.1以下の時と同様の手順で 影響範囲を確認しつつ作業する。
SQLite 3 カラムを削除する (～ver 3.34.1)

エラーになる例

主キーやユニークキーが設定してあるカラムを削除しようとすると、 以下のようなエラーになる

sqlite> .schema product
CREATE TABLE product (
    id         integer primary key
    , name     text    not null
    , quantity integer default 0
    , remark   text
);

sqlite> alter table product drop column id;
Error: cannot drop PRIMARY KEY column: "id"
                

ver 3.34.1 以下でカラムを削除したい場合は以下を参照のこと。
SQLite 3 カラムを削除する (～ver 3.34.1)

参考URL

• ALTER TABLE DROP COLUMN
  公式のカラム削除に関する情報
  https://www.sqlite.org/lang_altertable.html#alter_table_drop_column

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

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」カラムを削除する。

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

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;
                

ちゃんとした手順

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

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

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

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