【SQLite入門】テーブルから列を削除する

SQLiteでは残念ながら、ALTER TABLE での列の削除はサポートされていません。

以下では、列を削除する方法について記載しています。

1. 削除手順の概要

テーブルから直接列を削除することは出来ないため、大まかに次の手順で列の削除を行います。

列を削除した新しいテーブルを作成
旧テーブルから新しいテーブルにデータを移行
旧テーブルを削除
新しいテーブルをリネーム

2. 削除手順の詳細

1. トリガーやビューの定義を確認

旧テーブルを削除すると、テーブルを参照しているトリガーは削除されます。

また、ビューも手順の過程で削除するので、念のためテーブルを参照しているトリガーやビューを把握し、定義内容を退避します。

次のSQLで旧テーブルに関連するトリガーやビューの定義を確認することが出来ます。

SELECT * FROM sqlite_master WHERE sql like '%旧テーブル名%';

1	SELECT * FROM sqlite_master WHERE sql like '%旧テーブル名%';

2. 外部キー制約を無効にする

外部キー制約を有効にして運用している場合、外部キー制約を無効にします。

PRAGMA foreign_keys = OFF;

1	PRAGMA foreign_keys = OFF;

3. 列を削除した新しいテーブルを定義

旧テーブルデータを移行するために、列を削除した新しいテーブルを定義します。

4. 旧テーブルから新しいテーブルにデータ移行

次のSQLなどを使用して旧テーブルのデータを移行します。

INSERT INTO 新しいテーブル名(列名・・・) SELECT 列名・・・ FROM 旧テーブル名;

1	INSERT INTO 新しいテーブル名(列名・・・) SELECT 列名・・・ FROM 旧テーブル名;

5. ビューの削除

旧テーブルを参照しているビューを削除します。

DROP VIEW ビュー名;

1	DROP VIEW ビュー名;

6. 旧テーブルの削除

旧テーブルを削除します。

DROP TABLE 旧テーブル名;

1	DROP TABLE 旧テーブル名;

7. 新しいテーブル名の変更

新しいテーブル名を旧テーブル名に変更します。

ALTER TABLE 新しいテーブル名 RENAME TO 旧テーブル名;

1	ALTER TABLE 新しいテーブル名 RENAME TO 旧テーブル名;

8. ビューの再作成

手順5で削除したビューを再作成します。

もし、削除した列を参照している場合は、ビューの内容を書き換えて再作成します。

9. トリガーの再作成

旧テーブルを参照していた、トリガーを再作成します。

10. 外部キー制約のチェック

外部キー制約に不整合が生じていないかチェックします。

PRAGMA foreign_key_check;

1	PRAGMA foreign_key_check;

不整合が生じている場合は、どの制約で不整合が起きているか表示されるので適宜対応します。

11. 外部キー制約を有効にする

外部キー制約に不整合が生じていないことを確認したら外部キー制約を有効にします。

PRAGMA foreign_keys = ON;

1	PRAGMA foreign_keys = ON;

3. 実行例

テスト用のテーブルを作成して、実際に列の削除を行います。

以下の実行例では３テーブル、１ビューを使用します。

テーブル

３つのテーブル（tbl_a、tbl_b、tbl_c）を使用します。
全体では tbl_a → tbl_b → tbl_c という関係になります。
※ → は 1:多

定義

CREATE TABLE tbl_a (
 a_id INTEGER PRIMARY KEY,
 b_cnt INTEGER
);

CREATE TABLE tbl_b (
 b_id INTEGER PRIMARY KEY,
 a_id INTEGER,
 t1 TEXT,
 t2 TEXT,
 FOREIGN KEY(a_id) REFERENCES tbl_a(a_id)
);

CREATE TABLE tbl_c (
 c_id INTEGER PRIMARY KEY,
 b_id INTEGER,
 FOREIGN KEY (b_id) REFERENCES tbl_b(b_id)
);

CREATE TABLE tbl_a (

a_id INTEGER PRIMARY KEY,

b_cnt INTEGER

);

CREATE TABLE tbl_b (

b_id INTEGER PRIMARY KEY,

a_id INTEGER,

t1 TEXT,

t2 TEXT,

FOREIGN KEY(a_id) REFERENCES tbl_a(a_id)

);

CREATE TABLE tbl_c (

c_id INTEGER PRIMARY KEY,

b_id INTEGER,

FOREIGN KEY (b_id) REFERENCES tbl_b(b_id)

);

ビュー

tbl_a と tbl_b を結合した内容を表示する単純なビューです。

定義

CREATE VIEW v_ab AS 
SELECT * FROM tbl_a INNER JOIN tbl_b ON tbl_a.a_id = tbl_b.a_id;

1 2	CREATE VIEW v_ab AS SELECT * FROM tbl_a INNER JOIN tbl_b ON tbl_a.a_id = tbl_b.a_id;

トリガー

tbl_b のトリガーを作成します。
tbl_b にデータを追加すると、tbl_a の b_cnt列を更新するトリガーです。

定義

CREATE TRIGGER trg_increment_b_cnt INSERT ON tbl_b 
BEGIN
 update tbl_a set b_cnt = b_cnt + 1 where a_id = new.a_id; 
END;

CREATE TRIGGER trg_increment_b_cnt INSERT ON tbl_b

BEGIN

update tbl_a set b_cnt = b_cnt + 1 where a_id = new.a_id;

END;

テスト用データ

各テーブルに１つずつデータを登録します。

INSERT INTO tbl_a(a_id, b_cnt) values(1,0);
INSERT INTO tbl_b(b_id, a_id, t1, t2) values(1,1,"text1","text2");
INSERT INTO tbl_c(c_id, b_id) values(1,1);

INSERT INTO tbl_a(a_id, b_cnt) values(1,0);

INSERT INTO tbl_b(b_id, a_id, t1, t2) values(1,1,"text1","text2");

INSERT INTO tbl_c(c_id, b_id) values(1,1);

列名の変更

ここからは、上記で定義した tbl_bテーブル の t2列 の削除を行います。

1. トリガーやビューの定義を確認

定義内容に”tbl_b”を含むSQLの一覧を表示して、ビューやトリガーの定義を確認します。

sqlite> SELECT * FROM sqlite_master WHERE sql like '%tbl_b%';
type        name        tbl_name    rootpage    sql                                                                                                                      
----------  ----------  ----------  ----------  --------------------------------------------------------------------------------------------------------------------     
table       tbl_b       tbl_b       9           CREATE TABLE tbl_b ( b_id INTEGER PRIMARY KEY, a_id INTEGER, t1 TEXT, t2 TEXT, FOREIGN KEY(a_id) REFERENCES tbl_a(a_id) )
table       tbl_c       tbl_c       10          CREATE TABLE tbl_c ( c_id INTEGER PRIMARY KEY, b_id INTEGER, FOREIGN KEY (b_id) REFERENCES tbl_b(b_id) )                 
view        v_ab        v_ab        0           CREATE VIEW v_ab AS SELECT * FROM tbl_a INNER JOIN tbl_b ON tbl_a.a_id = tbl_b.a_id                                      
trigger     trg_increm  tbl_b       0           CREATE TRIGGER trg_increment_b_cnt INSERT ON tbl_b BEGIN update tbl_a set b_cnt = b_cnt + 1 where a_id = new.a_id; END

sqlite> SELECT * FROM sqlite_master WHERE sql like '%tbl_b%';

type name tbl_name rootpage sql

---------- ---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------

table tbl_b tbl_b 9 CREATE TABLE tbl_b ( b_id INTEGER PRIMARY KEY, a_id INTEGER, t1 TEXT, t2 TEXT, FOREIGN KEY(a_id) REFERENCES tbl_a(a_id) )

table tbl_c tbl_c 10 CREATE TABLE tbl_c ( c_id INTEGER PRIMARY KEY, b_id INTEGER, FOREIGN KEY (b_id) REFERENCES tbl_b(b_id) )

view v_ab v_ab 0 CREATE VIEW v_ab AS SELECT * FROM tbl_a INNER JOIN tbl_b ON tbl_a.a_id = tbl_b.a_id

trigger trg_increm tbl_b 0 CREATE TRIGGER trg_increment_b_cnt INSERT ON tbl_b BEGIN update tbl_a set b_cnt = b_cnt + 1 where a_id = new.a_id; END

2. 外部キー制約を無効にする

外部キー制約を無効にします。

PRAGMA foreign_keys = OFF;

1	PRAGMA foreign_keys = OFF;

3. 列を削除した新しいテーブルを定義

t2列を削除した、tbl_b_new テーブルを定義します。

CREATE TABLE tbl_b_new (
 b_id INTEGER PRIMARY KEY,
 a_id INTEGER,
 t1 TEXT,
 FOREIGN KEY (a_id) REFERENCES tbl_a(a_id)
);

CREATE TABLE tbl_b_new (

b_id INTEGER PRIMARY KEY,

a_id INTEGER,

t1 TEXT,

FOREIGN KEY (a_id) REFERENCES tbl_a(a_id)

);

4. 旧テーブルから新しいテーブルにデータ移行

tbl_b テーブルのデータを、次のSQLで tbl_b_new に移行します。

INSERT INTO tbl_b_new(b_id, a_id, t1) SELECT b_id, a_id, t1 FROM tbl_b;

1	INSERT INTO tbl_b_new(b_id, a_id, t1) SELECT b_id, a_id, t1 FROM tbl_b;

5. ビューの削除

v_ab ビューを削除します。

DROP VIEW v_ab;

1	DROP VIEW v_ab;

6. 旧テーブルの削除

tbl_b テーブルを削除します。

DROP TABLE tbl_b;

1	DROP TABLE tbl_b;

7. 新しいテーブル名の変更

tbl_b_new テーブル名を tbl_b に変更します。

ALTER TABLE tbl_b_new RENAME TO tbl_b;

1	ALTER TABLE tbl_b_new RENAME TO tbl_b;

8. ビューの再作成

v_ab ビューを再作成します。
※ SQLは最初に定義した v_ab と同じです。

9. トリガーの再作成

trg_increment_b_cnt トリガーを再作成します。
※ SQLは最初に定義した trg_increment_b_cnt と同じです。

10. 外部キー制約のチェック

PRAGMA foreign_key_check;

1	PRAGMA foreign_key_check;

11. 外部キー制約を有効にする

PRAGMA foreign_keys = ON;

1	PRAGMA foreign_keys = ON;

これで列の削除ができました。

4. まとめ

SQLiteで列の削除を行いました。

ALTER TABLE で列を削除できないため、列を削除するには手間がかかります。

不要になった列でも運用に支障がない場合は、列を削除せずそのまま残しておくのもありかと思います。