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

SQLite3 ALTER TABLE に関するまとめ

ver 3.35.4 時点

初級

SQLiteのALTER TABLEは出来ることが少なく、以下の4つだけ。

• テーブルの名前を変更する (RENAME TABLE)
• カラムの名前を変更する (RENAME COLUMN)
• 新規カラムを追加する (ADD COLUMN)
• カラムを削除する (DROP COLUMN)

中級

SQLiteでは特殊な手順を踏まなければならない操作。

• カラムの型を変更するには
• カラムに外部キー制約を追加するには
  • 新しいカラムを追加し、かつ外部キー制約を追加するには
• カラムの外部キー制約を削除するには

マニアック

ぶっちゃけ覚えなくていい。
必要になった時に調べるだけでOK。

• スキーマ文を編集する
• テーブル構成のバージョンを確認する

古いバージョンの情報

• カラムを削除する (～ver 3.34.1)
• カラムの変更や削除ができない仕様について (～ver 3.34.1)

参考URL

• ALTER TABLE
  公式のALTER TABLEに関するドキュメント
  https://www.sqlite.org/lang_altertable.html

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

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

SQLite3 ではテーブルに対してカラムの変更や削除ができない (～ver 3.34.1)

概要

SQLite ver 3.34.1 以下でテーブルスキーマ(構成)に対して行える操作は以下の3種類。

• テーブルの名前を変更する
• テーブルに新しいカラムを追加する
• テーブルのカラムの名前を変更する

他のOracleやSQLServerなどのDBMSで出来る以下のようなことは出来ない。

• テーブルのカラムを削除する
• テーブルのカラムの型を変更する
• テーブル間の外部キー制約を変更する
etc・・・

よって、テーブルスキーマを変更したい場合は、新しいテーブルを作成してそちらにデータを移す必要がある。

詳細

なぜこのような仕様なのかは公式の alter tableに関するページに記載してある。 これを読んでおくと、テーブルが変更できない理由や外部制約キーの仕様の理由がよくわかる。

ざっくり言うと、 SQLite3ではテーブルのスキーマを「sqlite_schema」というテーブルにDDLで保存している。 このメリットとして、スキーマを維持するコストが減る、 バージョン変わっても互換性を維持しやすい、ということがある。 デメリットとして、スキーマの変更が難しくなっている。

参考URL

• Why ALTER TABLE is such a problem for SQLite
  公式のalter table に関するドキュメント
  https://sqlite.org/lang_altertable.html#why_alter_table_is_such_a_problem_for_sqlite

• SQLite3 テーブル名を変更する
  https://sfnovicenotes.blogspot.com/2020/12/sqlite3_7.html
• SQLite3 新しいカラム(列)を追加する
  https://sfnovicenotes.blogspot.com/2020/12/sqlite3_8.html
• SQLite3 列の名前を変更する
  https://sfnovicenotes.blogspot.com/2020/12/sqlite3_7.html
• SQLite3 カラムを削除する
  https://sfnovicenotes.blogspot.com/2020/12/sqlite3_20.html
• SQLite3 カラムの型を変更する
  https://sfnovicenotes.blogspot.com/2021/01/sqlite3.html

SQLite3 新しいカラムを追加する (ADD COLUMN)

概要

SQLite3 でテーブルに新しいカラムを追加するSQL。
他のDBMS同様に「alter table～」を利用する。

追加したカラムは必ず最後に追加される。 位置を変更したい場合は、新しいテーブルを作成しなおしてデータを移すしかない。

複数のカラムを一括で追加することもできないので、 その場合は追加したいカラムの数だけ「alter table～」文を実行する。

構文

以下の3パターン。
「column_constraint」は「not null」や「pimary key」など。
理由がない限り、データ型は指定するようにする。

alter table table_name add column column_name;
                

alter table table_name add column column_name data_type;
                

alter table table_name add column column_name data_type column_constraint;
                

実行例

実行環境

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

「product」テーブルに「type」カラムを追加してtext型にする

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

sqlite> alter table product add column type text;

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

上記の.schemaコマンドの結果を見るとわかるが、 既存のスキーマに自動で追加されるので見づらくなってしまう。

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

参考URL

• ALTER TABLE ADD COLUMN
  公式のalter table に関するドキュメント
  https://www.sqlite.org/lang_altertable.html#alter_table_add_column

SQLite3 カラムの名前を変更する (RENAME COLUMN)

概要

SQLite 3 でカラムの名前を変更するSQL。
他のDBMS同様に「alter table～」を利用する。

alter table table_name rename old_name to new_name;
                

もしくは

alter table table_name rename column old_name to new_name; 
                

OracleやMySQLだと「alter table～modify～」でも変更できるが、SQLite 3 ではこれができない。 さらに言うと、「alter table～」でカラムのデータ型を変更することもできない。 (ver. 3.33.0 時点)

Sqlite 3 カラムの型を変更する

環境

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

実行例

カラム名「id」を「product_id」に変更する

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

sqlite> alter table product rename column id to product_id;

sqlite> .schema product
CREATE TABLE product
(
        product_id integer
        , name     text
        , quantity integer
        , remark   text
);
                

対象カラムを参照しているスキーマも変更される

ビュー、トリガー、外部キー制約に記載されているカラム名が変更される。

1. 「total_view」ビューが「product」テーブルの「quantity」を参照している
2. 「product」テーブルの「quantity」を「price」に変更すると 「total_view」も「price」を参照するよう変更される

sqlite> .tables
product     total_view

sqlite> -- # 1.
sqlite> .schema total_view
CREATE VIEW total_view
as
select
    sum(quantity)
from
    "product"
/* total_view("sum(quantity)") */;

sqlite> -- # 2.
sqlite> alter table product rename quantity to price;
sqlite> .schema total_view
CREATE VIEW total_view
as
select
    sum(price)
from
    "product"
/* total_view("sum(price)") */;
                

参考URL

• Command Line Shell For SQLite
  公式のコマンドラインツールに関するドキュメント
  https://www.sqlite.org/cli.html
• ALTER TABLE RENAME COLUMN
  公式のalter table に関するドキュメント
  https://www.sqlite.org/lang_altertable.html#alter_table_rename_column

SQLite3 テーブル名を変更する (RENAME TABLE)

概要

SQLite3でテーブル名を変更するDDL。 OracleやMySQLなどとほぼ変わらない。

alter table old_name rename to new_name;
                

実行例

環境

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

sqlite> .tables
product
sqlite> alter table product rename to item;
sqlite> .tables
item
                

対象テーブルを参照しているスキーマも一緒に変更される

ビュー、トリガー、外部キー制約など

1. 「total_view」ビューが「product」テーブルを参照している
2. 「product」を「item」に変更すると「total_view」のスキーマも変更される

sqlite> .tables
product     total_view

sqlite> -- # 1.
sqlite> .schema total_view
CREATE VIEW total_view
as
select
    sum(quantity)
from
    "product"
/* total_view("sum(quantity)") */;

sqlite> -- # 2.
sqlite> alter table product rename to item;
sqlite> .tables
item        total_view
sqlite> .schema total_view
CREATE VIEW total_view
as
select
    sum(quantity)
from
    "item"
/* total_view("sum(quantity)") */;
                

参考URL

• Command Line Shell For SQLite
  公式のコマンドラインツールに関するドキュメント
  https://www.sqlite.org/cli.html
• ALTER TABLE RENAME
  公式のalter table に関するドキュメント
  https://www.sqlite.org/lang_altertable.html#alter_table_rename

• SQLite3 alter table でテーブル名が変更できない (legacy_alter_table)
  https://sfnovicenotes.blogspot.com/2020/11/sqlite3-alter-table-legacyaltertable.html

SQLite 3 alter table でテーブル名が変更できない (legacy_alter_table)

概要

以下のような手順を踏むとエラーになってテーブル名が変更できない。

1. pragma legacy_alter_table=ON になっている
2. ビュー、トリガー、外部キーなどで参照されているテーブルの名前を変更する
3. pragma legacy_alter_table=OFF にする
4. 2.で変更したテーブルの名前を変更しようとするとエラーになる

解決するには pragma legacy_alter_table=ON の状態でテーブル名と参照している側のテーブル名を揃える必要がある

環境

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

エラーの再現手順

sqlite> -- # 1.
sqlite> .tables
product     total_view

sqlite> -- # 2.
sqlite> .schema total_view
CREATE VIEW total_view
as
select
    sum(quantity)
from
    "product"
/* total_view("sum(quantity)") */;

sqlite> -- # 3.
sqlite> pragma legacy_alter_table=on;

sqlite> -- # 4.
sqlite> alter table product rename to item;
sqlite> .tables
item        total_view

sqlite> -- # 5. 
sqlite> .schema total_view
CREATE VIEW total_view
as
select
    sum(quantity)
from
    "product";

sqlite> -- # 6.
sqlite> select * from total_view limit 1;
Error: no such table: main.

sqlite> -- # 7.
sqlite> pragma legacy_alter_table=off;
sqlite> alter table item rename to product;
Error: error in view total_view: no such table: main.product
                

1. 「product」テーブルと「total_view」ビューがある。
2. 「total_view」ビューは「product」テーブルを参照している。
3. 「legacy_alter_table」をonにする。
4. 「product」テーブルの名前を「item」に変更する。
5. 「legacy_alter_table」がonの状態でテーブル名を変更したため、「total_view」の参照先は「product」のまま。
6. この状態で「total_view」をselectすると、当然エラーになる。
7. 「legacy_alter_table」をoffにして「item」テーブルの名前を変更しようとするとエラーになる。

解決方法

以下の2通り。1の方が楽。

1. 「legacy_alter_table」をonにしてテーブル名を直してからoffにする。
2. テーブルを参照しているビュー、トリガー、外部キーを再作成する。

1の実行例

sqlite> -- # 1.
sqlite> pragma legacy_alter_table=on;
sqlite> alter table item rename to product;

sqlite> -- # 2.
sqlite> pragma legacy_alter_table=off;
                

1. 「legacy_alter_table」をonにして参照されているテーブルの名前を変更する。
2. 「legacy_alter_table」をoffにする。

参考URL

• 2. ALTER TABLE RENAME
  公式のalter table ～ rename ～ to に関するドキュメント
  https://sqlite.org/lang_altertable.html#altertabrename
• Pragma legacy_alter_table
  公式の legacy_alter_table に関するドキュメント
  https://www.sqlite.org/pragma.html#pragma_legacy_alter_table

• あさはか備忘録: SQLite3 テーブル名を変更する
  https://sfnovicenotes.blogspot.com/2020/12/sqlite3.html
• あさはか備忘録: SQLite3 legacy_alter_tableとは？
  https://sfnovicenotes.blogspot.com/2020/11/sqlite3-json.html