When the primary key encounters NULL

The primary key does not seem to have much to do with Null, because the general primary key settings are all notnull, but the combination of the two will lead to many unexpected situations, that is, unexpected reasons

The primary key does not seem to have much to do with Null, because the general primary key settings are not null, but when the two are combined, there will be many unexpected situations, that is, unexpected reasons

The primary key does not seem to have much to do with Null, because the general primary key settings are not null, but when the two are combined, there will be many unexpected situations, that is, unexpected because the results are not within the expected range, however, if you understand the basic principles, the entire process is reasonable.

Let's first demonstrate the problem.

First, create a table to create a unique index.

SQL> conn n1/n1
Connected.
SQL>
SQL> select * from cat;

No rows selected

SQL> create table test (x number, y number );

Table created.

SQL> create unique index ind_test on test (x, y );

Index created.

SQL> insert into test values (1, 2 );

1 row created.
If you insert duplicate data again, it is no doubt that an error will be thrown.

SQL> insert into test values (1, 2 );
Insert into test values (1, 2)
*
ERROR at line 1:
ORA-00001: unique constraint (N1.IND _test) violated

Then we test the null-related scenarios.
SQL> insert into test values (1, null );

1 row created.

SQL> insert into test values (null, 1 );

1 row created.
You can also insert two Null values.

SQL> insert into test values (null, null );

1 row created.
You can insert two null values again.
SQL> insert into test values (null, null );

1 row created.
But in turn, insert 1 again. If it is null, an error is thrown.

SQL> insert into test values (1, null );
Insert into test values (1, null)
*
ERROR at line 1:
ORA-00001: unique constraint (N1.IND _test) violated

Similarly, the same is true for null and 1.
SQL> insert into test values (null, 1 );
Insert into test values (null, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (N1.IND _test) violated

Insert two null values again.
SQL> insert into test values (null, null );

1 row created.
View the data in the test table as follows:

SQL> select * from test;

X Y
--------------------
1 2
1
1

6 rows selected.
There are 6 rows. The null Value column cannot be displayed.
To identify, we print rownum.

SQL> select rownum, x, y from test;

ROWNUM X Y
------------------------------
1 1 2
2 1
3 1
4
5
6

6 rows selected.
After testing the null value, let's look at the null String.
When you insert '', an error is thrown.

SQL> insert into test values (1 ,'');
Insert into test values (1 ,'')
*
ERROR at line 1:
ORA-00001: unique constraint (N1.IND _test) violated

Insert two empty strings, which have the same effect as null.
SQL> insert into test values ('','');

1 row created.
There is no problem with null and empty string combinations.

SQL> insert into test values (null ,'');

1 row created.

SQL> insert into test values ('','');

1 row created.
Check the data again. The number of null data rows increases significantly.

SQL> select rownum, x, y from test;

ROWNUM X Y
------------------------------
1 1 2
2 1
3 1
4
5
6
7
8
9

9 rows selected.

We can further look at the length of the null value, using length ()
SQL> select rownum, x, length (x), y, length (y) from test;

Rownum x length (X) y length (Y)
--------------------------------------------------
1 1 1 2 1
2 1 1
3 1 1
4
5
6
7
8
9

9 rows selected.
The length corresponding to the null value is not displayed.
If = is used to match an empty string, the effect is the same as that of null.

SQL> select * from test where x = '';

No rows selected
Let's take a look at the dump information. For the null column dump, the result is null.

1 * select rownum, x, y, dump (x) from test
SQL>/