[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語句不受影響.可以滿足生產系統需求.