SQLite3 での NULL および DEFAULT 制約の振る舞い、それと PRAGMA TABLE_INFO の dflt_value の値

動作確認環境

SQLite 3.37.0

振る舞いの確認

「NOT NULL」指定なし、「DEFAULT NULL」指定なし

sqlite> .schema test1
CREATE TABLE IF NOT EXISTS "test1" (
    "id"    INTEGER,
    "name"  TEXT,
    "number"    INTEGER,
    PRIMARY KEY("id" AUTOINCREMENT)
);

sqlite> INSERT INTO test1 (name, number) VALUES ('Smith', 10);
sqlite> INSERT INTO test1 (name) VALUES ('Smith');
sqlite> INSERT INTO test1 (number) VALUES (30);

sqlite> .mode box
sqlite> .nullvalue (NULL)
sqlite> SELECT * FROM test1;
┌────┬────────┬────────┐
│ id │  name  │ number │
├────┼────────┼────────┤
│ 1  │ Smith  │ 10     │
│ 2  │ Smith  │ (NULL) │
│ 3  │ (NULL) │ 30     │
└────┴────────┴────────┘

name, number コラムの定義は NULL なので、値を指定しない場合は NULL が挿入されます。

「NOT NULL」指定あり、「DEFAULT NULL」指定なし

sqlite> .schema test2
CREATE TABLE IF NOT EXISTS "test2" (
    "id"    INTEGER,
    "name"  TEXT NOT NULL,
    "number"    INTEGER NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);

sqlite> INSERT INTO test2 (name, number) VALUES ('Smith', 10);
sqlite> INSERT INTO test2 (name) VALUES ('Smith');
Error: stepping, NOT NULL constraint failed: test2.number (19)
sqlite> INSERT INTO test2 (number) VALUES (30);
Error: stepping, NOT NULL constraint failed: test2.name (19)

name, number コラムの定義は NOT NULL なので、値を指定しない場合はエラーになります。

sqlite> SELECT * FROM test2;
┌────┬───────┬────────┐
│ id │ name  │ number │
├────┼───────┼────────┤
│ 1  │ Smith │ 10     │
└────┴───────┴────────┘

「NOT NULL」指定あり、「DEFAULT NULL」指定あり

sqlite> .schema test3
CREATE TABLE IF NOT EXISTS "test3" (
    "id"    INTEGER,
    "name"  TEXT NOT NULL DEFAULT NULL,
    "number"    INTEGER NOT NULL DEFAULT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);

sqlite> INSERT INTO test3 (name, number) VALUES ('Smith', 10);
sqlite> INSERT INTO test3 (name) VALUES ('Smith');
Error: stepping, NOT NULL constraint failed: test2.number (19)
sqlite> INSERT INTO test3 (number) VALUES (30);
Error: stepping, NOT NULL constraint failed: test2.name (19)

name, number コラムの定義は NOT NULL なので、値を指定しない場合はエラーになります。 DEFAULT NULL は無視されるようです。

sqlite> SELECT * FROM test3;
┌────┬───────┬────────┐
│ id │ name  │ number │
├────┼───────┼────────┤
│ 1  │ Smith │ 10     │
└────┴───────┴────────┘

「NOT NULL」指定なし、「DEFAULT NULL」指定あり

sqlite> .schema test4
CREATE TABLE IF NOT EXISTS "test4" (
    "id"    INTEGER,
    "name"  TEXT DEFAULT NULL,
    "number"    INTEGER DEFAULT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);

sqlite> INSERT INTO test4 (name, number) VALUES ('Smith', 10);
sqlite> INSERT INTO test4 (name) VALUES ('Smith');
sqlite> INSERT INTO test4 (number) VALUES (30);

sqlite> SELECT * FROM test4;
┌────┬────────┬────────┐
│ id │  name  │ number │
├────┼────────┼────────┤
│ 1  │ Smith  │ 10     │
│ 2  │ Smith  │ (NULL) │
│ 3  │ (NULL) │ 30     │
└────┴────────┴────────┘

name, number コラムの定義は NULL なので、値を指定しない場合は NULL が挿入されます。

PRAGMA TABLE_INFO の確認

「NOT NULL」指定なし、「DEFAULT NULL」指定なし

sqlite> PRAGMA TABLE_INFO(test1);
┌─────┬────────┬─────────┬─────────┬────────────┬────┐
│ cid │  name  │  type   │ notnull │ dflt_value │ pk │
├─────┼────────┼─────────┼─────────┼────────────┼────┤
│ 0   │ id     │ INTEGER │ 0       │ (NULL)     │ 1  │
│ 1   │ name   │ TEXT    │ 0       │ (NULL)     │ 0  │
│ 2   │ number │ INTEGER │ 0       │ (NULL)     │ 0  │
└─────┴────────┴─────────┴─────────┴────────────┴────┘

「NOT NULL」指定あり、「DEFAULT NULL」指定なし

sqlite> PRAGMA TABLE_INFO(test2);
┌─────┬────────┬─────────┬─────────┬────────────┬────┐
│ cid │  name  │  type   │ notnull │ dflt_value │ pk │
├─────┼────────┼─────────┼─────────┼────────────┼────┤
│ 0   │ id     │ INTEGER │ 0       │ (NULL)     │ 1  │
│ 1   │ name   │ TEXT    │ 1       │ (NULL)     │ 0  │
│ 2   │ number │ INTEGER │ 1       │ (NULL)     │ 0  │
└─────┴────────┴─────────┴─────────┴────────────┴────┘

「NOT NULL」指定あり、「DEFAULT NULL」指定あり

sqlite> PRAGMA TABLE_INFO(test3);
┌─────┬────────┬─────────┬─────────┬────────────┬────┐
│ cid │  name  │  type   │ notnull │ dflt_value │ pk │
├─────┼────────┼─────────┼─────────┼────────────┼────┤
│ 0   │ id     │ INTEGER │ 0       │ (NULL)     │ 1  │
│ 1   │ name   │ TEXT    │ 1       │ NULL       │ 0  │
│ 2   │ number │ INTEGER │ 1       │ NULL       │ 0  │
└─────┴────────┴─────────┴─────────┴────────────┴────┘

「DEFAULT NULL」を指定した場合は、dflt_value はNULL値ではなく NULL という文字が返ります。

「NOT NULL」指定なし、「DEFAULT NULL」指定あり

sqlite> PRAGMA TABLE_INFO(test4);
┌─────┬────────┬─────────┬─────────┬────────────┬────┐
│ cid │  name  │  type   │ notnull │ dflt_value │ pk │
├─────┼────────┼─────────┼─────────┼────────────┼────┤
│ 0   │ id     │ INTEGER │ 0       │ (NULL)     │ 1  │
│ 1   │ name   │ TEXT    │ 0       │ NULL       │ 0  │
│ 2   │ number │ INTEGER │ 0       │ NULL       │ 0  │
└─────┴────────┴─────────┴─────────┴────────────┴────┘

NULL でないデフォルト値の場合

sqlite> .schema test5
CREATE TABLE IF NOT EXISTS "test5" (
    "id"    INTEGER,
    "name"  TEXT DEFAULT 'Smith',
    "number"    INTEGER DEFAULT 0,
    PRIMARY KEY("id" AUTOINCREMENT)
);

sqlite> PRAGMA TABLE_INFO(test5);
┌─────┬────────┬─────────┬─────────┬────────────┬────┐
│ cid │  name  │  type   │ notnull │ dflt_value │ pk │
├─────┼────────┼─────────┼─────────┼────────────┼────┤
│ 0   │ id     │ INTEGER │ 0       │ (NULL)     │ 1  │
│ 1   │ name   │ TEXT    │ 0       │ 'Smith'    │ 0  │
│ 2   │ number │ INTEGER │ 0       │ 0          │ 0  │
└─────┴────────┴─────────┴─────────┴────────────┴────┘

dflt_value は、文字列の場合 'Smith' のようにシングルクォートで囲まれた値が返ります。