| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: unique constraints
In article <370c72d2.13001294_at_192.86.155.100>,
tkyte_at_us.oracle.com wrote:
> A copy of this was sent to Prasad <prasad_at_inspire.net.nz>
> (if that email address didn't require changing)
> On Sun, 04 Apr 1999 23:19:20 +1200, you wrote:
>
> >Hai!
> >I want to know how oracle reads the null values
> >on a column defined with unique constraint . Plese clarify whether
> >null is treated as same or different on each row.
> >
> >I will be very much thankful for an early reply
> >kep
>
> the nulls are unique:
>
> SQL> create table t ( x int unique );
> Table created.
>
> SQL> insert into t values ( null );
> 1 row created.
>
> SQL> insert into t values ( null );
> 1 row created.
>
> SQL> drop table t ;
> Table dropped.
>
> SQL> create table t ( x int, y int, constraint u_xy unique(x,y) );
> Table created.
>
> SQL> insert into t values ( 1, null );
> 1 row created.
>
> SQL> insert into t values ( 2, null );
> 1 row created.
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
I think that the second part of the example needs expansion to show that Oracle will accept multiple unique keys where the entire key is null, but reject partially null keys where the non-null portion exists: (Comments added to list output)
ORA805> create table mark_test (fld1 number, fld2 number, 2 constraint mark_test_uk unique(fld1,fld2)) 3 /
Table created.
ORA805> insert into mark_test values (1,1) 2 /
1 row created.
ORA805> insert into mark_test values (1,2) 2 /
1 row created.
ORA805> insert into mark_test values (1,null) 2 /
1 row created.
ORA805> insert into mark_test values (2,1) 2 /
1 row created
ORA805> insert into mark_test values (2,2) 2 /
1 row created.
ORA805> insert into mark_test values (2,null) 2 /
1 row created.
ORA805> insert into mark_test values (null,null) 2 /
1 row created.
ORA805> REM Repeat some inserts from above
ORA805> insert into mark_test values (1,null)
2 /
insert into mark_test values (1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (MPOWEL01.MARK_TEST_UK) violated
REM We can not repeat the first column value with a null 2nd col REM but Unique keys where all columns are null are acceptableORA805> insert into mark_test values (null,null) 2 /
1 row created.
ORA805> insert into mark_test values (null,1) 2 /
1 row created.
ORA805> insert into mark_test values (null,1)
2 /
insert into mark_test values (null,1)
*
ERROR at line 1:
ORA-00001: unique constraint (MPOWEL01.MARK_TEST_UK) violated
REM Example showing switching of columns does not change outcome ORA805> spool off
Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Apr 05 1999 - 08:19:53 CDT
 |
 |