[20240228]约束novalidate.txt

--//生产系统建立一个字段not null约束,但是原来表中已经存在null值,不能直接建立not null约束,可以加入novalidate属性保证以后
--//DML语句不会出现null值.测试看看.

1.环境:
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.建立测试环境:
SCOTT@test01p> create table tx ( a number ,b number, vc varchar2(20));
Table created.

SCOTT@test01p> @ pk tx a
create unique index pk_tx on t1 (a);
alter table tx add constraint pk_tx primary key (a);
-- alter table tx modify constraint pk_tx disable;
-- alter table tx drop constraint PK_tx;
-- drop index pk_tx;
Index created.
Table altered.

SCOTT@test01p> insert into tx values (1,null,'1');
1 row created.

SCOTT@test01p> insert into tx values (2,2,'2');
1 row created.

SCOTT@test01p> commit ;
Commit complete.
--//分析略.
--//当前B字段允许null值.

SCOTT@test01p> alter table tx modify b not null ;
alter table tx modify b not null
*
ERROR at line 1:
ORA-02296: cannot enable (SCOTT.) - null values found

SCOTT@test01p> alter table tx modify b not null novalidate;
Table altered.

SCOTT@test01p> @ desc tx
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                               NOT NULL NUMBER
    2      B                                        NUMBER
    3      VC                                       VARCHAR2(20)
--//desc 无法知道b字段是否允许null值,可以查询user_constraints相关视图确定.

3.测试:

SCOTT@test01p> update tx set b = NULL where a=2;
update tx set b = NULL where a=2
              *
ERROR at line 1:
ORA-01407: cannot update ("SCOTT"."TX"."B") to NULL
--//现在无法再设置b=NULL.

COTT@test01p> insert into tx values (3,null,'3');
nsert into tx values (3,null,'3')
                        *
RROR at line 1:
RA-01400: cannot insert NULL into ("SCOTT"."TX"."B")
--//也无法再插入NULL.

SCOTT@test01p> select * from tx;
         A          B VC
---------- ---------- --------------------
         1            1
         2          2 2

SCOTT@test01p> update tx set vc = 'a'||to_char(a) where a=1;
1 row updated.

SCOTT@test01p> commit;
Commit complete.

SCOTT@test01p> select * from tx;
         A          B VC
---------- ---------- --------------------
         1            a1
         2          2 2

--//不涉及b字段update语句不受影响.可以满足生产系统需求.