[20140823]12c null與預設值.txt

[20140823]12c null與預設值.txt

--12c 當插入NULL時可以指定預設值.不知道為什麼設定這個特性,有點怪怪的.

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table t (id number,idx number default on null 100);
insert  into t(id) values (1);
insert  into t(id,idx) values (2,200);
insert  into t(id,idx) values (3,NULL);
commit;

SCOTT@test01p> select * from t;
        ID        IDX
---------- ----------
         1        100
         2        200
         3        100
--可以發現如果插入idx=NULL,實際上插入是預設值100.真不知道那個大客戶提出這種需求....

select obj#,col#,segcol#,name,default$,type# from sys.col$  where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;

OBJ#  COL#    SEGCOL# NAME  DEFAULT$        TYPE#
----- ----- ---------- ----- ---------- ----------
96076     1          1 ID    NULL                2
96076     2          2 IDX   100                 2

SCOTT@test01p> select dbms_metadata.get_ddl('TABLE', 'T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "ID" NUMBER,
        "IDX" NUMBER DEFAULT 100 NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

SCOTT@test01p> select constraint_name, constraint_type, deferrable, search_condition from user_constraints where table_name='T';
CONSTRAINT_NAME      C DEFERRABLE     SEARCH_CONDITION
-------------------- - -------------- ------------------------------
SYS_C0010756         C NOT DEFERRABLE "IDX" IS NOT NULL

--當DEFAULT NOT NULL 使用時 NOT NULL和NOT DEFERRABLE約束是隱含建立的.