View the powerful Concept from the introduction of NOTNULL integrity constraints

View the powerful Concept from the introduction of NOTNULL integrity constraints

Oracle Concept is the official book we learned on a daily basis. One reason is that there are many problems. In the final analysis, we can find some explanations and sources from Concept, I remember one time when Kamus answered a netizen's question (I forgot the specific question), he quoted a sentence in a chapter of Concept. Another reason is that the content is concise and concise. Some Usage Details are taken out with a few simple words or sentences, but they often contain rich meanings, it's a bit of a sound.

For example, when Chapter 5 introduces not null Integrity Constraints, there is a sentence:

You can only add a column with a not null constraint if the table does not contain any rows or if you specify a default value.

If the table has no data or contains the default value, you can add a not null constraint to the column. In a simple sentence, there is actually a lot of information. We use experiments to illustrate it.

Prerequisites: 11.2.0.4

SQL> create table tbl_null(2 id number primary key,3 name varchar2(5)4 );Table created.SQL> insert into tbl_null values(1, 'a');1 row created.SQL> insert into tbl_null values(2, '');1 row created.SQL> select * from tbl_null;ID NAME---------- -----1 a2

Here, a test table TBL_NULL is created, which contains a name field, VARCHAR2 type, which can be null. There are two records, one of which is empty.

Lab 1: When "no data in the table" is verified, the not null constraint can be added.
In this case, the table has a record whose name is null. if you add a non-null constraint to the record:

SQL> alter table tbl_null modify name not null;alter table tbl_null modify name not null*ERROR at line 1:ORA-02296: cannot enable (BISAL.) - null values found

Because the name field has a record that is null, it will prompt that a null value is found and execution is prohibited.
Run the following command after deleting the data:

SQL> truncate table tbl_null;Table altered.SQL> alter table tbl_null modify name not null;Table altered.

When there is no data in the table, the not null constraint can be added at will.

Lab 2: When "include default value" is verified, the not null constraint can be added.

SQL> select * from tbl_null; ID NAME---------- ----- 1 aSQL> alter table tbl_null modify name not null;Table altered.

If the name field of the data in the table has a value, the not null constraint can be added. It is easy to understand that Oracle does NOT know how to set these NULL values if the field value is empty and the not null constraint is set.

Lab 3: The record in the table has a NULL value, and the not null constraint is also added.
Then the problem arises. If the record in the table has a NULL value, the not null constraint must be added, but the data cannot be cleared according to experiment 1 or the default value cannot be assigned to experiment 2. What should I do?
You can use the enable novalidate option to add the not null constraint to enable non-NULL checks on existing data.

SQL> select * from tbl_null;ID NAME---------- -----1 a2SQL> alter table tbl_null modify name not null enable novalidate;Table altered.SQL> insert into tbl_null values(3, '');insert into tbl_null values(3, '')*ERROR at line 1:ORA-01400: cannot insert NULL into ("BISAL"."TBL_NULL"."NAME")SQL> select * from tbl_null;ID NAME---------- -----1 a2

It can be seen that an error is returned when a new null value is added, prompting that null values cannot be inserted, but the table actually contains a record with the name being null. That is, when the enable novalidate option is used, Oracle does not verify whether the historical data meets the constraints, but only verifies the new data.
Official explanation of enable novalidate:

The database checks the constraint, but it need not be true for all rows. Thus, existing rows can violate the constraint, but new or modified rows must conform to the rules.

? If the application has special processing restrictions on the field not null, you can UPDATE the old data with a NULL value as the default value to ensure that the field value in the table has a value, that is, the not null constraint is added when fields in the table have NULL values.

Lab 4: What is the difference between using and not using enable novalidate?
Further, let's take a look at the difference between using and not using the enable novalidate background.

1. Create a test table and simulate 5000000 rows of records:

SQL> declare 2 n number; 3 j number; 4 begin 5 for i in 1 .. 5000000 loop 6 insert into tbl_null values(i, 'A'); 7 n := mod(i, 100000); 8 if (n = 0) then 9 j := i/100000; 10 dbms_output.put_line('the '||j||' insert'); 11 commit; 12 end if; 13 end loop; 14 commit; 15 end; 16 /PL/SQL procedure successfully completed.SQL> select count(*) from tbl_null; COUNT(*)---------- 5000000

2. Test with or without enable novalidate:

SQL> alter table tbl_null modify name not null;Table altered.Elapse: 00:00:00.17SQL> alter table tbl_null_0 modify name not null enable novalidate;Table altered.Elapsed: 00:00:00.05

Roughly speaking, using enable novalidate is faster than not using it.

3. From the trace file 10046, the following SQL statement is executed first when enable novalidate is used or not used:

LOCK TABLE "TBL_NULL" IN EXCLUSIVE MODE NOWAIT

Add an exclusive lock to the TBL_NULL table in NOWAIT mode. If enable novalidate is not used, the following two additional operations related to TBL_NULL are performed:

select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "BISAL"."TBL_NULL" A where( "NAME" is null)

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("A") FULL("A") NO_PARALLEL_INDEX("A") */ 1 AS C1, CASE WHEN "A"."NAME" IS NULL THEN 1 ELSE 0 END AS C2 FROM "BISAL"."TBL_NULL" "A") SAMPLESUB

From these two SQL statements, we can see that he wants to retrieve all records whose name values are null and use full table scan. Although I am not quite clear about the true meaning of these two steps, it can be explained to some extent that some possible reasons for the use of enable novalidate is faster.

Summary:
The addition of the not null constraint seems very simple. The official documentation also provides a few simple words to describe, but we can still test each condition by experiment, I deeply understand some of the reasons behind it. In addition, many knowledge points in Oracle are related to each other. eygle once said that "Learning Oracle from points and planes" is what I think is the truth.
Of course, what makes me better understand is that I still have too much knowledge to learn. As a beginner in Oracle, I am still on the road... Mutual encouragement.