关于唯一键(Unique)

SQL> create table test_ranbo(id number);
 
Table created
 
SQL> alter table test_ranbo add constraint test_ranbo$uk unique (id);
 
Table altered

SQL> insert into test_ranbo(id) values(null);
 
1 row inserted
 
SQL> insert into test_ranbo(id) values(null);
 
1 row inserted

可以插入两个null.

下面建个包含两列外键的例子。

SQL> truncate table test_Ranbo;
 
Table truncated
 
SQL> alter table test_ranbo drop constraint test_ranbo$uk;
 
Table altered
 
SQL> alter table test_ranbo add Name varchar2(30);
 
Table altered
 
SQL> desc test_ranbo;
Name Type         Nullable Default Comments
---- ------------ -------- ------- --------
ID   NUMBER       Y                       
NAME VARCHAR2(30) Y                       
 
SQL> alter table test_ranbo add constraint test_ranbo$uk unique (id ,name);
 
Table altered
 
SQL> insert into test_ranbo values(1,null);
 
1 row inserted
 
SQL> insert into test_ranbo  values(1,null);
 
insert into test_ranbo  values(1,null)
 
ORA-00001: unique constraint (DBOWN.TEST_RANBO$UK) violated

再由测试外键是三列的情况。

SQL> truncate table test_ranbo;
 
Table truncated
 
SQL> alter table test_ranbo drop constraint test_ranbo$uk;
 
Table altered
 
SQL> alter table test_ranbo add type varchar2(30);
 
Table altered
 
SQL> desc test_ranbo;
Name Type         Nullable Default Comments
---- ------------ -------- ------- --------
ID   NUMBER       Y                       
NAME VARCHAR2(30) Y                       
TYPE VARCHAR2(30) Y                       
 
SQL> alter table test_ranbo add constraint test_ranbo$uk unique (id,name,type);
 
Table altered
 
SQL> insert into test_ranbo(id,name,type) values(1,null,null);
 
1 row inserted
 
SQL> /
 
insert into test_ranbo(id,name,type) values(1,null,null)
 
ORA-00001: unique constraint (DBOWN.TEST_RANBO$UK) violated

SQL> insert into test_ranbo(id,name,type) values(null,null,null);
 
1 row inserted
 
SQL> /
 
1 row inserted

SQL> insert into test_ranbo(id,name,type) values(null,'a',null);
 
1 row inserted
 
SQL> /
 
insert into test_ranbo(id,name,type) values(null,'a',null)
 
ORA-00001: unique constraint (DBOWN.TEST_RANBO$UK) violated

 

 

 

由此可见，在唯一键包含两列以上的情况下,只要有了一列是非null,那么其他列的null可以理解为一个确定的值,唯一键会起作用，只有唯一键的所有列都是null值的时候不管插入多少行相同的记录唯一键都不起作用。

 

from:http://space.itpub.net/13387766/viewspace-608824