23cで追加された"DEFAULT ON NULL for UPDATE Statements"という機能ですが、似たようなものは12cR1からありました。
ただし12cR1で追加されたのはINSERT向けのみで、UPDATEには対応していませんでした。
今回の機能追加でINSERTに加えUPDATEも"DEFAULT ON NULL"が使えるようになっています。
23cマニュアルの新機能一覧の該当箇所はこちら、詳しい説明はこちらで「ON NULL」とページ内検索してください(いずれも英語です)。
テーブル作成
それでは新機能を使ったテーブルを作成していきます。
CREATE TABLE t1 (
id NUMBER,
c1 VARCHAR2(15) DEFAULT 'red',
c2 VARCHAR2(15) DEFAULT ON NULL FOR INSERT ONLY 'blue',
c3 VARCHAR2(15) DEFAULT ON NULL FOR INSERT AND UPDATE 'yellow'
);
c1～c3で異なるDEFAULT句を利用していますが、それぞれの内容は以下の通りです。
- c1：古くからあるDEFAULT句
- c2：12cR1から使えるINSERT向けのDEFAULT ON NULL句。"FOR INSERT ONLY"という指定は23cで追加されたもの
- c3：23cで追加されたINSERTおよびUPDATE向けのDEFAULT ON NULL句
ちなみにc2とc3に対しては"DEFAULT ON NULL"句を指定したことで、下記2つの制約が暗黙的に設定されます。
- NOT NULL
- NOT DEFERRABLE
INSERTの動作確認
テストデータを挿入しながら、c1～c3の挙動の違いを見てみます。
INSERT INTO t1 (id, c1, c2, c3) VALUES (1, null, null, null);
INSERT INTO t1 (id) VALUES (2);
SELECT * FROM t1 ORDER BY 1;
ID C1 C2 C3
---------- --------------- --------------- ---------------
1 blue yellow
2 red blue yellow
1行目はすべてnullにしました。
c1はDEFAULT句のみなので空欄に、c2とc3はINSERT向けの"DEFAULT ON NULL"が設定されているため、デフォルト値の"blue"および"yellow"が入っています。
2行目はすべて未指定です。
c2とc3の挙動は1行目と変わりませんが、c1は通常のDEFAULT句で指定された"red"が入っています。
UPDATEの動作確認
先程INSERTしたデータに対してUPDATEをかけていきます。
c1～c3それぞれの挙動を個別に確認します。
まずはc1です。
SQL> UPDATE t1 SET c1 = null;
2行が更新されました。
SQL> SELECT * FROM t1 ORDER BY 1;
ID C1 C2 C3
---------- --------------- --------------- ---------------
1 blue yellow
2 blue yellow
c1はnull値に対して特に制約もないため、指定した通り空白になりました。
次にc2です。
SQL> UPDATE t1 SET c2 = null;
UPDATE t1 SET c2 = null
*
行1でエラーが発生しました。:
ORA-01407: ("TEST2"."T1"."C2")をNULLには更新できません。
c2には12cR1で追加されたINSERT向け"DEFAULT ON NULL"が設定されているため、UPDATEで入力されるnullには有効ではありません。
一方でc2には冒頭で述べたNOT NULL制約、およびNOT DEFERRABLE制約が設定されているため、上記のようにエラーとなります。
次にc3です。
SQL> UPDATE t1 SET c3 = null;
2行が更新されました。
SQL> SELECT * FROM t1 ORDER BY 1;
ID C1 C2 C3
---------- --------------- --------------- ---------------
1 blue yellow
2 blue yellow
c3はINSERTに加えUPDATEにも"DEFAULT ON NULL"が有効となっているため、エラーとならずDEFAULT値の"yellow"が入力されました。
念のため一度他の値に変更し、再度nullでUPDATEを掛けてみます。
SQL> UPDATE t1 SET c3 = 'white';
2行が更新されました。
SQL> SELECT * FROM t1 ORDER BY 1;
ID C1 C2 C3
---------- --------------- --------------- ---------------
1 blue white
2 blue white
SQL> UPDATE t1 SET c3 = null;
2行が更新されました。
SQL> SELECT * FROM t1 ORDER BY 1;
ID C1 C2 C3
---------- --------------- --------------- ---------------
1 blue yellow
2 blue yellow
一度c3に"white"と指定すれば"white"になり、その後"null"を指定すると"DEFAULT ON NULL"で指定した"yellow"に変わりました。
このようにINSERTとUPDATE双方に対応した"DEFAULT ON NULL"ですが、トリガーを使った抜け穴?があります。
以下のようにトリガーを作成すると、c3のデフォルト値をトリガーによって上書き出来てしまいます。
デフォルト値を"null"で上書きした場合、NOT NULL制約に引っかかるためエラーとなります。
SQL> CREATE OR REPLACE TRIGGER t1_t
BEFORE INSERT OR UPDATE ON t1
FOR EACH ROW
BEGIN
:new.c3 := NULL;
END;
/
トリガーが作成されました。
SQL> INSERT INTO t1 (id, c3) VALUES (3, null);
INSERT INTO t1 (id, c3) VALUES (3, null)
*
行1でエラーが発生しました。:
ORA-01400: ("TEST2"."T1"."C3")にはNULLは挿入できません。
SQL> INSERT INTO t1 (id, c3) VALUES (3, 'green');
INSERT INTO t1 (id, c3) VALUES (3, 'green')
*
行1でエラーが発生しました。:
ORA-01400: ("TEST2"."T1"."C3")にはNULLは挿入できません。
SQL> INSERT INTO t1 (id) VALUES (3);
INSERT INTO t1 (id) VALUES (3)
*
行1でエラーが発生しました。:
ORA-01400: ("TEST2"."T1"."C3")にはNULLは挿入できません。
SQL> UPDATE t1 SET c3 = null;
UPDATE t1 SET c3 = null
*
行1でエラーが発生しました。:
ORA-01407: ("TEST2"."T1"."C3")をNULLには更新できません。
SQL> UPDATE t1 SET c3 = 'white';
UPDATE t1 SET c3 = 'white'
*
行1でエラーが発生しました。:
ORA-01407: ("TEST2"."T1"."C3")をNULLには更新できません。
このように、どうやってもNOT NULL制約違反となるためエラーが返ってきます。
以上、簡単ですが23cで追加されたSQLの新機能"DEFAULT ON NULL for UPDATE Statements"でした。