Oracle: Empty string and NOT NULL column

0. 前言

最近的專案又回到 Oracle, 印象中, 在作空字串與 Null 的處理時, 有一些要注意的地方. 所以, 就整理了本篇及 上一篇 的文章.

茲區分以下幾個部份作說明.

• 1. SQL 指令 (NOT NULL)
• 2. 輸出結果 (NOT NULL)
• 3. 個別執行 SELECT 指令的截圖 (NOT NULL)
• 4. 結論 (NOT NULL)
• 5. 參考文件

1. SQL 指令 (NOT NULL)

DROP TABLE USER1.TESTNOTNULL;
/
CREATE TABLE USER1.TESTNOTNULL (
    ID    NUMBER(3,0)     DEFAULT 0 NOT NULL,
    NAME  CHAR(10)    DEFAULT ' ' NOT NULL,
    ADDRESS   NVARCHAR2(20)     DEFAULT ' ' NOT NULL,
   CONSTRAINT PK_TESTNOTNULL PRIMARY KEY ( ID )
);
/
INSERT INTO TESTNOTNULL(ID, NAME, ADDRESS)  
VALUES (1, 'JASPER', 'TAIPEI');
-- NAME 及 ADDRESS 給預設值.
INSERT INTO TESTNOTNULL(ID)
VALUES (2);
-- NAME 給預設值, ADDRESS 填入 5  個 bytes 的空白
INSERT INTO TESTNOTNULL(ID, ADDRESS)
VALUES (3, LPAD(' ', 5, ' '));
/
-- 情境1 -------------------------
PROMPT -- [情境1] -------------------------;
SELECT ID
    , '[' || NAME || ']' AS NAME1
    , '[' || ADDRESS || ']' AS ADDRESS1
FROM TESTNOTNULL;
-- 情境2 -------------------------
PROMPT -- [情境2] -------------------------;
-- 3 筆資料的 NAME 長度都是 10 --> 因為 CHAR(10), 會被補空白.
-- 3 筆資料的 ADDRESS 長度不同 --> 因為 VARCHAR2(20), 不會被補空白. 以實際資料值為準
SELECT ID
    , LENGTH(NAME) AS LEN1
    , LENGTH(ADDRESS) AS LEN2
FROM TESTNOTNULL;
/
-- INSERT 失敗情境_A -------------------------
PROMPT -- [INSERT 失敗情境_A] -------------------------;
-- NAME 填入空字串, ADDRESS  填入 3  個 bytes 的空白 --> 會失敗
INSERT INTO TESTNOTNULL(ID, NAME, ADDRESS)
VALUES (4, '', LPAD(' ', 3, ' ') );
-- INSERT 失敗情境_B -------------------------
PROMPT -- [INSERT 失敗情境_B] -------------------------;
-- NAME 給預設值, ADDRESS 填入空字串 --> 會失敗
INSERT INTO TESTNOTNULL(ID,  ADDRESS)
VALUES (5, '' );
/
                

2. 輸出結果 (NOT NULL)

Table USER1.TESTNOTNULL 已刪除.
Table USER1.TESTNOTNULL 已建立.

1 資料列 已插入.
1 資料列 已插入.
1 資料列 已插入.

-- [情境1] -------------------------
        ID NAME1        ADDRESS1              
---------- ------------ ----------------------
         1 [JASPER    ] [TAIPEI]              
         2 [          ] [ ]                   
         3 [          ] [     ]               

-- [情境2] -------------------------
        ID       LEN1       LEN2
---------- ---------- ----------
         1         10          6
         2         10          1
         3         10          5

-- [INSERT 失敗情境_A] -------------------------
命令的第 37 行開始發生錯誤 -
INSERT INTO TESTNOTNULL(ID, NAME, ADDRESS)
VALUES (4, '', LPAD(' ', 3, ' ') )
錯誤報告 -
ORA-01400: 無法將空值插入 ("USER1"."TESTNOTNULL"."NAME")

-- [INSERT 失敗情境_B] -------------------------
命令的第 42 行開始發生錯誤 -
INSERT INTO TESTNOTNULL(ID,  ADDRESS)
VALUES (5, '' )
錯誤報告 -
ORA-01400: 無法將空值插入 ("USER1"."TESTNOTNULL"."ADDRESS")
                

3. 個別執行 SELECT 指令的截圖 (NOT NULL)

[情境1]

[情境2]

4. 結論 (NOT NULL)

不論是 CHAR or VARCHAR2 只要欄位是 不允許 NULL, 就不能在 INSERT, UPDATE 時, 填入空字串, 因為空字串被視為 NULL 值.

5. 參考文件

• 01. 黑暗執行緒, KB-詭異的NOT IN查詢，原來是NULL搞鬼
• 02. 黑暗執行緒, ORACLE, 你把空字串怎麼了?
• 03. 黑暗執行緒, KB－當 WHERE AND/OR 條件遇上 NULL
• 04. Oracle Help Center, Database SQL Reference: Nulls
• 05. Oracle Help Center, SQL Language Reference: Nulls