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)