not null constraint和check constriant的问题及分析(64天)

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;

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

查看数据字典。 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)

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-05-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于long类型的转换(r3笔记第84天)

在oracle中对于long类型的处理时很纠结的。最开始引入这个数据类型的时候是对原有数据类型的补充,但是后面发现还是碰到了一些问题,使用Lob类型代替了。但是...

328100
来自专栏IT技术精选文摘

MySQL的索引是什么?怎么优化?

10740
来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之数据字典元数据(r4笔记第33天)

MySQL和Oracle虽然在架构上有很大的不同,但是如果从某些方面比较起来,它们有些方面也是相通的。 毕竟学习的主线是MySQL,所以会从MySQL的角度来对...

30860
来自专栏程序员宝库

Mysql - JOIN 详解

一个完整的SQL语句中会被拆分成多个子句,子句的执行过程中会产生虚拟表(vt),但是结果只返回最后一张虚拟表。从这个思路出发,我们试着理解一下JOIN查询的执行...

44030
来自专栏黑泽君的专栏

day05_MySQL学习笔记_02

    注:每个表中至少要有一个主键。 主键用于唯一地标识表中的每一条记录,可以定义一列或多列为主键。 

10320
来自专栏LanceToBigData

MySQL(五)之DDL(数据定义语言)与六大约束

前言 前面在数据库的讲解中,其实很多东西都非常的细节,在以前的学习过程中我都是没有注意到的。可能在以后的工作中会碰到所以都是做了记录的。 接下来,我将分享的是M...

29590
来自专栏静默虚空的博客

Mysql 快速指南

20720
来自专栏chenssy

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

10530
来自专栏Java帮帮-微信公众号-技术文章全总结

MySQL全部知识点(1)

? 数据库 1 数据库概念(了解) 1.1 什么是数据库 数据库就是用来存储和管理数据的仓库! 数据库存储数据的优先: 可存储大量数据; 方便检索; 保持数据...

32350
来自专栏乐沙弥的世界

Oracle 性能相关常用脚本(SQL)

在缺乏的可视化工具来监控数据库性能的情形下,常用的脚本就派上用场了,下面提供几个关于Oracle性能相关的脚本供大家参考。以下脚本均在Oracle 10g测试通...

14120

扫码关注云+社区

领取腾讯云代金券