2022年4月2日土曜日

SQLite3 nullを登録できないようにカラムを設定する(NOT NULL制約)

概要

sqlite3で カラムにnullを登録できないように制限するSQL。
nullを登録出来ないようにする = 値の登録が必須、となる。 sqlite では空白文字とnullが区別されることに注意すること。

nullを登録できないようにするには、テーブル作成時にカラムに対して「NOT NULL」制約を指定すればいい。

既存のテーブルに対して「NOT NULL」制約を追加する方法もあるが、かなり面倒。

環境
  • Windows 10 64bit
  • SQLite3 (3.38.0) Command-Line Shell

テーブル作成時に「NOT NULL」制約を設定する

サンプルのテーブルとして、「id」「name」カラムを入力必須とし、 「mail」カラムは入力必須ではないものとする。

「test」テーブル
カラム名 型 制約
id int primary key
not null
name text not null
mail text

このテーブルを作成するためには以下のSQLを実行する。
カラム名の後にNOT NULLと記述することで、そのカラムに「NOT NULL」制約を設定出来る。 


create table test (
    id     int  primary key not null
    , name text not null
    , mail text
);

これで「test」テーブルにデータを登録する際、 「id」カラムと「name」カラムは入力必須となる。

以下は上記のテーブルにデータを登録する例。

  1. 「mail」カラムは空でも登録出来る
  2. 「id」や「name」を空で挿入とすると「NOT NULL」制約でエラーになる
  3. 挿入だけでなく更新時も「NOT NULL」制約でエラーになる

sqlite> -- # 1.
sqlite> insert into test (id, name) values (1, 'sample_name_1');

sqlite> -- # 2.
sqlite> insert into test (id, mail) values (2, 'sample@example.com');
Runtime error: NOT NULL constraint failed: test.name (19)

sqlite> -- # 3.
sqlite> update test set (id, mail) values (null, 'sample@example.com');
Parse error: near "values": syntax error
    update test set (id, mail) values (null, 'sample@example.com');
                 error here ---^

既存のテーブルのカラムに「NOT NULL」制約を設定/解除する

既存のテーブルのカラムに「NOT NULL」制約を追加する少し手間がかかる。
と言うのも、SQLite3では ALTER TABLE で「NOT NULL」制約を設定することが出来ない。

「NOT NULL」制約を設定する具体的な方法はカラムの型を変更する方法と同じ。
解除するには設定する方法と逆の手順にすればいい。

以下は比較的簡単なテーブルでの変更例。

  • 「NOT NULL」制約を設定した状態のテーブルを別名で作成する。
  • 元のテーブルから新規作成したテーブルへデータをコピーする。
  • 元のテーブルを削除する。
  • 新規作成したテーブルの名前を変更する。

sqlite< .schema test
CREATE TABLE IF NOT EXISTS "test" (
    id     int  primary key not null
    , name text not null
    , mail text
);
sqlite< create table new_test (
   ...<     id     int  primary key not null
   ...<     , name text not null
   ...<     , mail text not null
   ...< );
sqlite< insert into
   ...<     new_test
   ...< select
   ...<     *
   ...< from
   ...<     test
   ...< ;
sqlite< drop table test;
sqlite< alter table new_test rename to test;

元のデータにnullが含まれているとinsert ... into ... の時点で「NOT NULL」制約でエラーになるので補完しておくこと。 


sqlite< .nullvalue [null]
sqlite< .mode box
sqlite< select * from test;
┌────┬──────┬────────┐
│ id │ name │  mail  │
├────┼──────┼────────┤
│ 1  │ name │ [null] │
└────┴──────┴────────┘
sqlite< insert into
   ...<     new_test
   ...< select
   ...<     *
   ...< from
   ...<     test
   ...< ;
Runtime error: NOT NULL constraint failed: new_test.mail (19)

上記は簡単な実行例だが、外部キー制約や参照しているビューなどがあるためさらに複雑な手順になる。 その手順については以下を参照のこと。

その他 実行例で使用しているコマンドはこちら。

参考URL

Posted by at
Labels: , ,