Oracle Database 23cで追加されたSQLの新機能"DEFAULT ON NULL for UPDATE Statements"をFree版で試してみた

23cで追加された"DEFAULT ON NULL for UPDATE Statements"という機能ですが、似たようなものは12cR1からありました。
ただし12cR1で追加されたのはINSERT向けのみで、UPDATEには対応していませんでした。
今回の機能追加でINSERTに加えUPDATEも"DEFAULT ON NULL"が使えるようになっています。
23cマニュアルの新機能一覧の該当箇所は[こちら](https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/application-development.html#GUID-A9386E91-0F3D-4B69-91A9-BB7AEF02461A)、詳しい説明は[こちら](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6)で「ON NULL」とページ内検索してください(いずれも英語です)。

### テーブル作成

それでは新機能を使ったテーブルを作成していきます。

```sql
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の挙動の違いを見てみます。

```sql
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
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
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
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
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
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"でした。