当主键碰到NULL

主键和Null看似没有多大的关系，因为一般的主键设置都是not null,但是把两者结合起来，会有很多意想不到的情况，说是意想不到是因

主键和Null看似没有多大的关系，因为一般的主键设置都是not null,但是把两者结合起来，会有很多意想不到的情况，说是意想不到是因为结果不在预期范围，但是如果明白了基本的原理，整个过程又在情理之中。

我们先来演示一下问题。

首先创建一个表，创建唯一性索引。

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.
再次插入重复的数据，这个肯定会抛错是毫无疑问的。

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

然后我们开始测试null相关的场景。
SQL> insert into test values(1,null);

1 row created.

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

1 row created.
插入两个Null值，也是可以的。

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

1 row created.
再次插入两个null值，还是可以的。
SQL> insert into test values(null,null);

1 row created.
但是反过来再次插入1,null的时候就抛错了。

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

同理，null,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

再次插入两个null值。
SQL> insert into test values(null,null);

1 row created.
查看表test中的数据，如下：

SQL> select *from test;

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

 

 6 rows selected.
可以看到有6行。null值列看不到任何显示。
 为了标识，我们打印出rownum来。

SQL> select rownum,x,y from test;

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

6 rows selected.
测试完了null值相关的，我们来看看空串''的情况。
 插入''的时候就会抛错。

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

插入两个空串，和null的效果是一样的。
SQL> insert into test values('','');

1 row created.
 null和空串组合，也没有问题。

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

1 row created.

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

1 row created.
再次查看数据，null值的数据行明显增多。

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.

我们可以再进一步，查看null值的长度，使用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.
可以看到null值对应的length没有任何显示。
 如果用=来匹配空串，和null的效果一样，匹配不了。

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

no rows selected
我们还是来看看dump的信息吧，，对于null列dump的结果就是null

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