SQLiteでは残念ながら、ALTER TABLE での列の削除はサポートされていません。
以下では、列を削除する方法について記載しています。
目次
1. 削除手順の概要
テーブルから直接列を削除することは出来ないため、大まかに次の手順で列の削除を行います。
- 列を削除した新しいテーブルを作成
- 旧テーブルから新しいテーブルにデータを移行
- 旧テーブルを削除
- 新しいテーブルをリネーム
2. 削除手順の詳細
1. トリガーやビューの定義を確認
旧テーブルを削除すると、テーブルを参照しているトリガーは削除されます。
また、ビューも手順の過程で削除するので、念のためテーブルを参照しているトリガーやビューを把握し、定義内容を退避します。
次のSQLで旧テーブルに関連するトリガーやビューの定義を確認することが出来ます。
1 |
SELECT * FROM sqlite_master WHERE sql like '%旧テーブル名%'; |
2. 外部キー制約を無効にする
外部キー制約を有効にして運用している場合、外部キー制約を無効にします。
1 |
PRAGMA foreign_keys = OFF; |
3. 列を削除した新しいテーブルを定義
旧テーブルデータを移行するために、列を削除した新しいテーブルを定義します。
4. 旧テーブルから新しいテーブルにデータ移行
次のSQLなどを使用して旧テーブルのデータを移行します。
1 |
INSERT INTO 新しいテーブル名(列名・・・) SELECT 列名・・・ FROM 旧テーブル名; |
5. ビューの削除
旧テーブルを参照しているビューを削除します。
1 |
DROP VIEW ビュー名; |
6. 旧テーブルの削除
旧テーブルを削除します。
1 |
DROP TABLE 旧テーブル名; |
7. 新しいテーブル名の変更
新しいテーブル名を旧テーブル名に変更します。
1 |
ALTER TABLE 新しいテーブル名 RENAME TO 旧テーブル名; |
8. ビューの再作成
手順5で削除したビューを再作成します。
もし、削除した列を参照している場合は、ビューの内容を書き換えて再作成します。
9. トリガーの再作成
旧テーブルを参照していた、トリガーを再作成します。
10. 外部キー制約のチェック
外部キー制約に不整合が生じていないかチェックします。
1 |
PRAGMA foreign_key_check; |
不整合が生じている場合は、どの制約で不整合が起きているか表示されるので適宜対応します。
11. 外部キー制約を有効にする
外部キー制約に不整合が生じていないことを確認したら外部キー制約を有効にします。
1 |
PRAGMA foreign_keys = ON; |
3. 実行例
テスト用のテーブルを作成して、実際に列の削除を行います。
以下の実行例では3テーブル、1ビューを使用します。
テーブル
3つのテーブル(tbl_a、tbl_b、tbl_c)を使用します。
全体では tbl_a → tbl_b → tbl_c という関係になります。
※ → は 1:多
定義
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 を結合した内容を表示する単純なビューです。
定義
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列を更新するトリガーです。
定義
1 2 3 4 |
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; |
テスト用データ
各テーブルに1つずつデータを登録します。
1 2 3 |
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の一覧を表示して、ビューやトリガーの定義を確認します。
1 2 3 4 5 6 7 |
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. 外部キー制約を無効にする
外部キー制約を無効にします。
1 |
PRAGMA foreign_keys = OFF; |
3. 列を削除した新しいテーブルを定義
t2列を削除した、tbl_b_new テーブルを定義します。
1 2 3 4 5 6 |
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 に移行します。
1 |
INSERT INTO tbl_b_new(b_id, a_id, t1) SELECT b_id, a_id, t1 FROM tbl_b; |
5. ビューの削除
v_ab ビューを削除します。
1 |
DROP VIEW v_ab; |
6. 旧テーブルの削除
tbl_b テーブルを削除します。
1 |
DROP TABLE tbl_b; |
7. 新しいテーブル名の変更
tbl_b_new テーブル名 を 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. 外部キー制約のチェック
1 |
PRAGMA foreign_key_check; |
11. 外部キー制約を有効にする
1 |
PRAGMA foreign_keys = ON; |
これで列の削除ができました。
4. まとめ
SQLiteで列の削除を行いました。
ALTER TABLE で列を削除できないため、列を削除するには手間がかかります。
不要になった列でも運用に支障がない場合は、列を削除せずそのまま残しておくのもありかと思います。