MariaDB講座 / NULL・DEFAULT・NOT NULL・CHECKで入力規則を定める

NULLは不明・未収集などの欠損を表し、0や空文字とは違います。DEFAULTは省略時の値、NOT NULLは欠損の禁止、CHECKは条件の検証なので、それぞれの責任に応じて組み合わせます。

値がない状態と入力の省略を分ける

NULLを一律に空文字へ変えると区別を失います。在庫が未確認なのに0を既定にすると、売り切れという誤った意味になる場合もあります。この実習は新商品の初期在庫を0とする方針です。通常の文字列処理を使い、EMPTY_STRING_IS_NULLなどの互換モードは有効にしません。

多くの通常比較はNULLがあると未知の結果NULLになります。検査は= NULLではなくIS NULLを使い、検索はNULLの比較で詳しく扱います。

既定値と制約を組み合わせる

DEFAULT 0は省略やDEFAULT指定に値を補いますが、明示したNULLを何でも0に変える規則ではありません。NOT NULLは空文字、0、負数を一般には禁止しません。CHECK(stock >= 0)も、NULLを許す列ではそれだけで欠損を禁止できません。

必ず既知で非負の在庫が必要ならNOT NULLとCHECKを組み合わせます。アプリケーションの検証は分かりやすい案内に役立ち、DB制約は別のクライアントやファイルからの書き込みにも境界を設けます。どちらかだけがすべての役割を代替するわけではありません。

有効な入力を完全な例で確認する

course_userで実習用サーバーへ接続し、SQLの中で確認します。

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

元のモードとCHECK設定を保存し、strictモードと検査を明示します。古い版では構文が受け入れられるだけで有効性を断定せず、実際の版と動作も確認します。

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET @saved_check_constraints = @@SESSION.check_constraint_checks;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET SESSION check_constraint_checks = ON;
CREATE TABLE validation_lab (
  item_id INT PRIMARY KEY,
  item_name VARCHAR(100) NOT NULL,
  note VARCHAR(100) NULL DEFAULT NULL,
  stock INT NOT NULL DEFAULT 0,
  CONSTRAINT ck_validation_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO validation_lab (item_id, item_name, note) VALUES
  (1, 'Notebook', NULL),
  (2, 'Mug', '');
SELECT item_id, note IS NULL AS note_is_null,
       note = '' AS note_is_empty, stock
FROM validation_lab ORDER BY item_id;
CREATE TABLE nullable_check_lab (
  item_id INT PRIMARY KEY,
  stock INT NULL,
  CONSTRAINT ck_nullable_stock CHECK (stock >= 0)
) ENGINE=InnoDB;
INSERT INTO nullable_check_lab VALUES (1, NULL);
SELECT item_id, stock IS NULL AS stock_is_null FROM nullable_check_lab;

一行目のnote_is_nullは1、note_is_emptyはNULL、二行目は0と1です。両方のstockは省略したので0になります。nullable_check_labではstockがNULLでもCHECKが偽とはならず保存できます。NOT NULLを付ける理由がここにあります。

失敗例を一文ずつ確認する

通常例と分け、必須の名前へNULLを指定します。

INSERT INTO validation_lab (item_id, item_name) VALUES (3, NULL);

次は名前を入れたうえで在庫の条件を破ります。

INSERT INTO validation_lab (item_id, item_name, stock) VALUES (3, 'Desk lamp', -1);

最初はNULL禁止、次はstockの制約違反となり、どちらもvalidation_labは2行のままです。失敗したID3は保存されていないため二つ目にも使えます。エラー文の表現より、どの列・制約で失敗したかを読みます。AUTO_INCREMENTやTIMESTAMPにはNULLの特別な扱いもあるので、この普通の列の結果を全型へ一般化しません。

空白と検査の有効性を確認する

NOT NULLの文字列でも空文字は入り得ます。必要ならCHECK(CHAR_LENGTH(TRIM(item_name)) > 0)のような条件を追加しますが、通常のTRIMがすべてのUnicode空白や不可視文字を除くとは限りません。

SHOW CREATE TABLE validation_lab;
SELECT @@SESSION.sql_mode, @@SESSION.check_constraint_checks;

意味の分かる制約名はエラー箇所の特定に役立ちます。check_constraint_checksを無効にすると宣言した規則を破るデータを入れられる場合があります。書き込むためだけに検証を止めず、入力と規則を見直します。再有効化だけで既存の不正行がすべて再検証・修復されるとも限りません。一意性の規則は別に設定します。

設定を戻して再実習する

保存した同じ接続で復元します。

SET SESSION check_constraint_checks = @saved_check_constraints;
SET SESSION sql_mode = @saved_sql_mode;

再接続後は保存変数を適用せず設定を読み直します。表の定義、選択中のDB、保存すべき行を確認してから、実習用二表に限って削除します。

DROP TABLE shopcourse.validation_lab, shopcourse.nullable_check_lab;

削除は通常のROLLBACKでは戻りません。最初のproducts表は保持し、正常例を再実行するときも失敗例を途中へ混ぜないでください。

関連する公式リファレンス

• null values（公式ドキュメント）
• constraint（公式ドキュメント）