not null constraint和check constriant的問題及分析

oracle的constraint有6類，如下。
但是基於列的constraint主要有 type 為C,P,R,U 接觸比較多的。

今天來和大家討論check constraint和not null constraint,它們的constraint type都為C,但是實際應用中還是有很大的差別。

Type Code	Type Description	Acts On Level
C	Check on a table	Column
O	Read Only on a view	Object
P	Primary Key	Object
R	Referential AKA Foreign Key	Column
U	Unique Key	Column
V	Check Option on a view	Object

有一天開發人員反饋，說有一個表的某個欄位有問題，標記為not null的，但是透過desc來檢視的時候，顯示是可以為null的。
欄位table_type是設定了Not null的，但是透過desc顯示卻沒有。
SQL> desc tt
 Name                                      Null?                    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL     VARCHAR2(30)
 TABLE_TYPE                                                     VARCHAR2(11)

下面簡單做一個示例來重現一下。
SQL> create table tt as select *from cat;
Table created.

SQL> desc tt
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                         VARCHAR2(11)
建立好之後，檢視constraint， table_type上是沒有任何約束的。
SQL> col search_condition format a30 
SQL>  select constraint_name,constraint_type,search_condition from user_constraints where table_name='TT';
CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - ------------------------------
SYS_C001310402                 C "TABLE_NAME" IS NOT NULL

設定not null constraint
SQL> alter table tt modify(table_type not  null);
Table altered.
再次檢視，constraint的名字是系統自動生成的，約束已經生成。
SQL>  select constraint_name,constraint_type,search_condition from user_constraints where table_name='TT';

CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - ------------------------------
SYS_C001310402                 C "TABLE_NAME" IS NOT NULL
SYS_C001310403                 C "TABLE_TYPE" IS NOT NULL

desc來檢視是沒有問題的。
SQL> desc ttst
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                NOT NULL VARCHAR2(11)

如果我們刪除not null constraint，然後這樣新增。

SQL> alter table tt modify(table_type null);
Table altered.
SQL> select constraint_name,constraint_type,search_condition from user_constraints where table_name='TT';
CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - ------------------------------
SYS_C001310402                 C "TABLE_NAME" IS NOT NULL
關鍵是這一句sql
SQL>  alter table tt add constraint tt_con_c check(table_type is not null);
Table altered.

表達的意思一樣，都是設定table_type不可以為Null
但是檢視constraint資料字典是，發現search condition顯示的是小寫的table_type is not null，和上一行的not null constraint有一些不一樣。


SQL>  select constraint_name,constraint_type,search_condition from user_constraints where table_name='TT';
CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - ------------------------------
SYS_C001310402                 C "TABLE_NAME" IS NOT NULL
TT_CON_C                           C table_type is not null
如果我sql語句寫成大寫，就看不出來了。

使用desc來看一下,not null的地方沒有了標註。會給使用帶來一些誤導。
SQL> desc tt
 Name                                      Null?            Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                                 VARCHAR2(11)

當然了，check constraint和not null constraint的區別還不在這一個地方
我如果對check constraint想取消 not null設定，會報下面的錯誤。
SQL> alter table tt modify(table_type null);
alter table tt modify(table_type null)
                      *
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

到此為止，大家應該明白check constraint和not null constraint的一些差別了。
然後我們更進一步來檢視為什麼desc的時候顯示 not null有問題。可以在user_tab_cols中發現端倪。
在資料字典中有一個專門的欄位來標記not null的屬性，如果是Not null constraint的話，這個值就是N
SQL>  select column_name ,nullable from user_tab_cols where table_name='TT';
COLUMN_NAME          NUL
-------------------- ---
TABLE_NAME           N
TABLE_TYPE           Y

問題似乎找到了，那改怎麼排查呢。
可以使用下面的sql語句來簡單的排查一下，下面的sql會對所有的check constraint做一個檢查，對錶中列對應的constraint進行一個簡單的篩查。因為search_condidtion是Long型別，所以不能使用like之類的模糊查詢了。
對於結果需要自己來判斷一下，從下面的輸出來看，table_type這個欄位對應的seach_condition是 table_type is not null但是在資料字典中註冊的not null屬性為Y,是一個潛在的問題。
SQL> select  con_col.table_name,con_col.constraint_name,user_cons.search_condition,con_col.column_name, user_cons.constraint_type,tab_col.nullable
    from user_cons_columns con_col, user_tab_cols tab_col,user_constraints  user_cons 
    where con_col.table_name = tab_col.table_name
and     con_col.column_name = tab_col.column_name
and     con_col.table_name = user_cons.table_name 
and    con_col.constraint_name = user_cons.constraint_name
and    user_cons.constraint_type='C'
and    tab_col.nullable='Y'
and     con_col.table_name='TT'  
   /

TABLE_NAME                     CONSTRAINT_NAME                SEARCH_CONDITION               COLUMN_NAME          CON NUL
------------------------------ ------------------------------ ------------------------------ -------------------- --- ---
TT                             TT_CON_C                                   table_type is not null         TABLE_TYPE           C   Y
不過話說回來，這個constraint是可以正常使用的，不過會給日常使用帶來一些誤導。
解決方法就是能夠重新建立not null constraint
使用下面的語句來建立指定名字的Not null constraint.
alter table tt drop constraint tt_con_c;
檢視資料字典。
SQL> select column_name ,nullable from user_tab_cols where table_name='TT';
COLUMN_NAME          NUL
-------------------- ---
TABLE_NAME           N
TABLE_TYPE           N

SQL> select constraint_name,constraint_type,search_condition from user_constraints where table_name='TT';
CONSTRAINT_NAME                CON SEARCH_CONDITION
------------------------------ --- ------------------------------
AAADSF                         C   "TABLE_NAME" IS NOT NULL
TT_CON_NN                      C   "TABLE_TYPE" IS NOT NULL

最後用一個desc來收尾
SQL> desc tt
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 TABLE_TYPE                                            NOT NULL VARCHAR2(11)