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

开发提了一个数据库变更需求,新增一字段,没有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新增默认值非空字段使用数据字典存储的特性,并且做了扩展支持,满足范围更大了。

小问题隐藏了大智慧。

时间: 2024-10-02 06:12:46

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

[20120410]9i下索引与空值查询以及非空约束.txt

[20120410]9i下索引与空值查询以及非空约束.txt 前几天在优化9i的一条sql语句时,发现一个奇怪的现象,查询条件是is null,发现竟然可以使用索引,感觉很奇怪,再仔细看原来查询字段存在一个非空约束,对比了10g以及11g的版本,感觉10g与11g改进不少. 测试如下: 1.建立测试环境: SQL> select * from v$version; BANNER -----------------------------------------------------------

oracle-初学ORACLE数据库,关于非空约束问题请教大家。

问题描述 初学ORACLE数据库,关于非空约束问题请教大家. 建表的时候有些字段设置了not null,但是插入记录时并没有哪一行插入记录,为什么会报错了?01400, 比如所有A/B/C三个字段,A设置类not null 但是我要插入记录时并不需要给A插入记录,只想给B/C插入记录.insert int table_name(b,c) values(value1,values2); 这样,报错了,无法将null插入A这个很不理解,请求大神,帮助一下. 解决方案 你已经把A设置为非空了,当插入这

(再贴)在DataGridView中如何进行唯一性和非空约束处理

问题描述 在数据库中的物理表上分别为两个字段设置了NOTNULL和UNIQUE约束数据在UI上以DataGridView呈现,用户可以在直接在其中添加删除修改当用户的输入违反唯一性和非空性约束时,我应该在哪里处理?如何处理?我的意思是说:1.事先就为DataGridView的数据源中的字段设置好唯一性和非空约束,然后捕获DataGridView的异常2.还是不为DataGridView的数据源中的字段设置这些,而是当用户点击确定时,用代码逐行严正数据源中的数据是否符合约束然后再写入数据库 解决方

在DataGridView中如何进行唯一性和非空约束处理

问题描述 在数据库中的物理表上分别为两个字段设置了NOTNULL和UNIQUE约束数据在UI上以DataGridView呈现,用户可以在其中进行添加.删除.修改操作当用户的输入违反唯一性和非空性约束时,我应该在哪处理?如何处理? 解决方案 解决方案二:在用户添加,修改的时候验证,是空的就不让他写入.唯一性就在提交时根据他输入的值去数据库看下有没有,有了也不让操作.

iBatis 插入非空字段的问题

问题描述 最近才开始研究iBatis,无意中发现用iBatis向数据库表中插入数据的时候,有时候出现问题:比如下面的情况数据库表 A 字段:id int 自增 字段:name varchar 非空 默认 " " 字段:pwd varchar 非空 默认 "1"实体类A 字段 id,name,pwd getter() setter() ...不加赘述sqlMap.xml 中的insert语句<insert id="insertSql" par

[20170516]nvl与非NULL约束.txt

[20170516]nvl与非NULL约束.txt --前几天做的测试http://blog.itpub.net/267265/viewspace-2137853/,实际上差异没有这个大,因为第2个多数是常量. --今天测试nvl与非NULL约束的问题. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ --------

数据库-vb.net提示未能启用约束。一行或多行中包含违反非空、唯一或外键约束的值。

问题描述 vb.net提示未能启用约束.一行或多行中包含违反非空.唯一或外键约束的值. 图片说明图片说明图片说明图片说明 解决方案 就是这个字段是唯一的.或者不能为空的.或者指向的外键必须存在等等,但是你插入的数据不满足. 解决方案二: 实在找不出问题在哪里了,麻烦你给看看吧,我上传了4张截图 解决方案三: 看你2,3两个图后,你完全没有数据验证 name,remark,mon没判断是否为空,date不是日期格式,mon是否可以转换成int,还有字符串长度判断

在Word 2010中筛选非空字段收件人记录

在Word 2010文档中进行邮件合并时,有时只需选择特定字段非空的收件人作为合并对象.例如只选择"职务"字 段含有内容的收件人,操作步骤如下所述: 第1步,打开Word 2010文档窗口,切换到"邮件"功能区.在"开始邮件合并"分组中单击"编 辑收件人列表"按钮,如图2011080401所示. 图2011080401 单 击"编辑收件人列表"按钮 小提示:如果"编辑收件人列表"按钮不可

在Word2010中筛选非空字段收件人记录

在Word2010文档中进行邮件合并时,有时只需选择特定字段非空的收件人作为合并对象.例如只选择"职务"字段含有内容的收件人,操作步骤如下所述: 第1步,打开Word2010文档窗口,切换到"邮件"功能区.在"开始邮件合并"分组中单击"编辑收件人列表"按钮,如图1所示. 电脑教程 图1 单击"编辑收件人列表"按钮小提示:如果"编辑收件人列表"按钮不可用,则需要在"开始邮件合并&