新增非空约束字段在不同版本中的演进

开发提了一个数据库变更需求，新增一字段，没有NOT NULL非空约束，但有默认值为NULL。看起来有些奇怪，因为若字段允许NULL，其默认值就是NULL，不用显示声明，可以创建一个无DEFAULT NULL的新增字段再查看desc表结构，就可以证明这点。

基于这问题，引申出的NOT NULL字段问题还有不少，也是比较容易忽视的一些细节，例如杨长老最近连续发表过两篇关于NOT NULL字段的文章确实很有启发，

非空字段空值对查询的影响
http://yangtingkun.net/?p=1481
非空字段空值的产生
http://yangtingkun.net/?p=1483

具体各位可以参考这两篇博文，简单总结一下，11.2.0.3的库， 1.使用where type is null和is not null得到的记录结果判断值为非空。 2.使用dump(type)和nvl(type, ‘is null’)得到的记录结果判断值为空。 表定义中此字段为DEFAULT ” NOT NULL，事实证明(2)是正确的，之所以有(1)的结论，原因是CBO太智能了。 对于IS NOT NULL，type字段定义为NOT NULL，此SQL明显违反了表中的约束条件，则会在执行计划最上层增加一个NULL IS NOT NULL恒为假的条件，根本不需要真正执行这个SQL，直接返回0条记录。 对于IS NULL，由于查询条件满足约束的条件，因此Oracle会做全表扫描，并且省略了type is not null的过滤，直接返回所有记录，就造成了type非空的假象。 出现以上问题的核心，还是为何有为空的记录存储于有NOT NULL非空约束的表中。原因就是11g新特性，新增一个有默认值的NOT NULL约束的字段，默认值不会像以前一样，插入每条记录中，而是会存储于一张数据字典表sys.ecol$，Oracle允许NOT NULL列默认值为NULL，因此对于11g来说，需要禁止DEFAULT为NULL的这种行为。

这种新增非空约束字段在不同版本中确实有一些细节的变化，下面做一些简单测试。 首先，创建测试表，插入一条数据，新增列为NOT NULL且默认值是”的字段：

create table bisal (id number);
insert into bisal values(1);
alter table bisal add name varchar2(10) default '' not null;

10.2.0.3库，从报错信息看ORA-01407，不能更新NAME列为空，可以看出此时是要将表中已存在记录的新列name做UPDATE设置为默认值的操作，由于有非空约束，因此不允许。

这里写图片描述

11.2.0.1库，可以新增字段，表中已存记录该值确实为空，即允许一个有NOT NULL约束的字段包含NULL值。

这里写图片描述

关于这新特性的好处，可以参考之前写的 《alter table新增字段操作究竟有何影响？(上下篇)》 http://blog.csdn.net/bisal/article/details/45418303 http://blog.csdn.net/bisal/article/details/49182025

12.1.0.2库，我们可以看出和10g一样，禁止新增一个默认值为NULL的NOT NULL约束字段，但报错信息变了，ORA-01758: table must be empty to add mandatory (NOT NULL) column，这个错误号在之前的版本有定义，不是新号。

这里写图片描述

根据错误提示，我们删除表中数据，再新增字段，可以增加，但不能再插入一条NULL至这个非空约束字段。

这里写图片描述

我们再看下官方文档的描述，11g中对于新增默认值字段的描述部分，明确指出NOT NULL约束包含默认值的情况下，是将默认值存储于数据字典中。

这里写图片描述

12c中描述允许为空的字段，若有默认值，不会更新已存数据，而是会借助数据字典完成存储，这种新特性的适用范围更广了。

这里写图片描述

由于我没有12c的sys真实环境，朋友可以自己尝试下，有机会我会自己再验证下。

至此，12c修复了11g中这个非空约束字段允许保存空值的bug，同时又支持11g新增默认值非空字段使用数据字典存储的特性，并且做了扩展支持，满足范围更大了。

小问题隐藏了大智慧。