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

oracle的constraint有6類，如下。

但是基于列的constraint主要有 type 為C,P,R,U 接觸比較多的。

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

<b>Type Code</b>

<b>Type Description</b>

<b>Acts On Level</b>

C

Check on a table

Column

O

Read Only on a view

Object

P

Primary Key

R

Referential AKA Foreign Key

U

Unique Key

V

Check Option on a view

有一天開發人員回報，說有一個表的某個字段有問題，标記為not null的，但是通過desc來檢視的時候，顯示是可以為null的。

字段table_type是設定了Not null的，但是通過desc顯示卻沒有。

SQL&gt; desc tt

 Name                                      Null?                    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                NOT NULL     VARCHAR2(30)

 TABLE_TYPE                                                     VARCHAR2(11)

下面簡單做一個示例來重制一下。

SQL&gt; create table tt as select *from cat;

Table created.

 Name                                      Null?    Type

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 TABLE_TYPE                                         VARCHAR2(11)

建立好之後，檢視constraint， table_type上是沒有任何限制的。

SQL&gt; col search_condition format a30 

SQL&gt;  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&gt; alter table tt modify(table_type not  null);

Table altered.

再次檢視，constraint的名字是系統自動生成的，限制已經生成。

SYS_C001310403                 C "TABLE_TYPE" IS NOT NULL

desc來檢視是沒有問題的。

SQL&gt; desc ttst

 TABLE_TYPE                                NOT NULL VARCHAR2(11)

如果我們删除not null constraint，然後這樣添加。

SQL&gt; alter table tt modify(table_type null);

SQL&gt; select constraint_name,constraint_type,search_condition from user_constraints where table_name='TT';

關鍵是這一句sql

SQL&gt;  alter table tt add constraint tt_con_c check(table_type is not null);

表達的意思一樣，都是設定table_type不可以為Null

但是檢視constraint資料字典是，發現search condition顯示的是小寫的table_type is not null，和上一行的not null constraint有一些不一樣。

TT_CON_C                           C table_type is not null

如果我sql語句寫成大寫，就看不出來了。

使用desc來看一下,not null的地方沒有了标注。會給使用帶來一些誤導。

 Name                                      Null?            Type

 TABLE_TYPE                                                 VARCHAR2(11)

當然了，check constraint和not null constraint的差別還不在這一個地方

我如果對check constraint想取消 not 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&gt;  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&gt; 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;

alter table tt modify(table_type varchar2(11) constraint tt_con_nn not null);

檢視資料字典。

SQL&gt; select column_name ,nullable from user_tab_cols where table_name='TT';

TABLE_TYPE           N

CONSTRAINT_NAME                CON SEARCH_CONDITION

------------------------------ --- ------------------------------

AAADSF                         C   "TABLE_NAME" IS NOT NULL

TT_CON_NN                      C   "TABLE_TYPE" IS NOT NULL

最後用一個desc來收尾

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 TABLE_NAME                                            NOT NULL VARCHAR2(30)

 TABLE_TYPE                                            NOT NULL VARCHAR2(11)