[20240228]约束novalidate.txt

[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语句不受影响.可以满足生产系统需求.