SQLiteで既存テーブルに制約を追加する

り

2024年7月24日 21:19

SQLiteとは

SQLiteは軽量なSQLデータベースエンジンを実装するC言語ライブラリです。セットアップが必要なく、データベースは単一のファイルに格納されます。

ALTER TABLEの概要

基本

SQLiteではALTER TABLEコマンドを提供しています。このコマンドは既存のテーブルに対して操作をすることができ、以下の操作をサポートしています。

ALTER TABLE RENAME: テーブル名を変更する
ALTER TABLE RENAME COLUMN: 列の名前を変更する
ALTER TABLE ADD COLUMN: テーブルに列を追加する
ALTER TABLE DROP COLUMN: 列を削除する

しかし既存テーブルのカラムに新しい制約等を追加するためには、ALTER TABLEコマンドを使うことができません。

解決策

解決策を以下のproductsテーブルを使って見ていきましょう。

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT UNIQUE,
    category TEXT,
    price REAL NOT NULL
);

productsのproduct_nameカラムに COLLATE NOCASE （大文字小文字を区別せずユニーク）の制約をつけるにはどのようにしたら良いでしょうか？

product_name TEXT COLLATE NOCASE UNIQUE

解決策①: 新しいテーブルを作成する方法

既存のテーブルのカラムに制約を追加する場合、新しいテーブルを作成してデータを移行するのが最も一般的な方法です。以下のような流れになります。

新しいテーブル(例: products_backup) を作成
データを移行
古いテーブル(例: products)を削除
新しいテーブルをリネーム

product_nameカラムに COLLATE NOCASE制約をつけるマイグレーションスクリプトは以下のようになります

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;

-- 現在のテーブルのバックアップ
CREATE TABLE products_backup AS SELECT * FROM products;

-- 新しいテーブルを作成
CREATE TABLE products_new (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT COLLATE NOCASE UNIQUE,
    category TEXT,
    price REAL NOT NULL
);

-- データを新しいテーブルに移行
INSERT INTO products_new (product_id, product_name, category, price)
SELECT product_id, product_name, category, price FROM products_backup;

-- 古いテーブルを削除
DROP TABLE products;

-- 新しいテーブルを元の名前にリネーム
ALTER TABLE products_new RENAME TO products;

COMMIT;
PRAGMA foreign_keys=on;

解決策②: トリガーを使用して制約を実現する方法

既存のテーブルを変更せずに実現したい場合、INSERT・UPDATEのトリガーを使用して対象のカラムが条件を満たしていることを保証する方法があります。

以下はproductsテーブルに新しいレコードを追加時、procutsテーブルの既存のレコードを更新時毎回以下のSQLが走り条件を満たしていない場合はエラーが発生するようになります。

CREATE TRIGGER unique_product_name_insert_trigger
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'Product name must be unique (case-insensitive)')
    WHERE EXISTS (
        SELECT 1
        FROM products
        WHERE LOWER(product_name) = LOWER(NEW.product_name)
    );
END;

CREATE TRIGGER unique_product_name_update_trigger
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'Product name must be unique (case-insensitive)')
    WHERE EXISTS (
        SELECT 1
        FROM products
        WHERE LOWER(product_name) = LOWER(NEW.product_name)
        AND product_id != NEW.product_id
    );
END;