| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Any way to do "unique or null"?
A copy of this was sent to roy_at_popmail.med.nyu.edu (Roy Smith)
(if that email address didn't require changing)
On Tue, 08 Sep 1998 11:45:04 -0400, you wrote:
>I've got a column which I want to allow to be null, but if there is a
>value in it, I want to ensure that value is unique amongst the non-null
>entries. Can a constraint be used to do this?
Yes, the NULLS will be unique amongst themselves ( NULL is neither EQUAL nor NOT EQUAL to NULL). so for example:
SQL> create table test ( x int unique ); Table created.
SQL> insert into test values ( NULL );
1 row created.
SQL> insert into test values ( NULL );
1 row created.
SQL> insert into test values ( 1 );
1 row created.
SQL> insert into test values ( 2 );
1 row created.
SQL> insert into test values ( 2 );
insert into test values ( 2 )
*
ERROR at line 1:
ORA-00001: unique constraint (TKYTE.SYS_C0024553) violated
So, the 2 nulls went in, the number 1 went in, but only one of the 2 number 2's could make it due to the unique constraint...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Sep 08 1998 - 11:30:32 CDT
 |
 |