Re: NULLs

vc wrote:
> > SQL> insert into tmptbl (colA, colB) values ('1', '');
> > 1 row created.
> > SQL> insert into tmptbl (colA, colB) values ('1', '');
> > insert into tmptbl (colA, colB) values ('1', '')
> > *
> > ERROR at line 1:
> > ORA-00001: unique constraint (MTRACK.TMPTBLIDX) violated
> >
> > The null values in colB violate the unique index, so at some level these
> > two nulls are considered "equal". This behavior surprised me the first
> > time I encountered it.
>
> Yes, it's appears contrary to what the SQL'92 standard says about
> multicolumn unique constraints (its language is quite cryptic though),
> but it has always been this way in Oracle.

Can you tell us what part of the SQL '92 standard it says this? I took
a look at the standard, or rather the interpretation of it, by C.J.
Date, "A Guide to The SQL Standard", 3rd ed. reprinted with corrections
December 1994, Addison Wesley. On p.235 in the section "Duplicate
Elimination", it says

.... Left and Right are defined to be duplicates of one another if and
only if, for all i in the range 1 to n, either "Li = Ri" is true, or Li
and Ri are both null.

Here his "Left" and "Right" correspond to two rows (feel free to
correct my understanding) and Li and Ri refer to each column of the two
rows. The above statement in Yong's language says these two rows are
duplicates

"a" 123 "some string"
"a" 123 "some string"

So are these two

null null null
null null null

But not these two

"a" 123 null
"a" 123 null

That is, you can't mix a null in any column. Oracle seems to follow the
standard quite well:

SQL> create table allnulltest (a number, b number);

Table created.

SQL> create unique index unq_allnulltest on allnulltest (a, b);

Index created.

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

1 row created.

SQL> /

1 row created.

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

1 row created.

SQL> /
insert into allnulltest values (1, null)
*
ERROR at line 1:
ORA-00001: unique constraint (YHUANG.UNQ_ALLNULLTEST) violated

Yong Huang

.

Relevant Pages

  • Re: 2 NULLs violate Uniqueness?
    ... This is a detail where SQL Server does not follow the SQL standard. ... Create a view of the nonnull items and put a unique index on it: ... Use a computed column that equals the primary key when F is null, ...
    (microsoft.public.sqlserver.programming)
  • Re: Duplicating data in continuous subform linked to SQL
    ... tables to a SQL 2008 DB that was upsized with Access 2003 to SQL 2005 then ... have a primary key or that Access has some trouble finding it or using it; ... using this second unique index as the primary key. ... you should take a look with the SQL-Server Profiler to see ...
    (microsoft.public.access.adp.sqlserver)
  • Re: join tables sequencialy
    ... i have 2 tables with the exact field structure and names, ... One thing that may not be obvious: there is *nothing* magical about SQL. ... local table in Access with the same 240 fields; you can set a unique Index on ... you can remove the duplicates later if need be. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Unique Columns
    ... right clicked on the field and selected Indexes/Keys ... The SQL has an ID but would also like to have another ... CREATE UNIQUE INDEX IX_tablename_columnname_U ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: NULLs
    ... >>> The null values in colB violate the unique index, ... > Can you tell us what part of the SQL '92 standard it says this? ... > Date, "A Guide to The SQL Standard", 3rd ed. reprinted with corrections ... Left and Right are defined to be duplicates of one another if and ...
    (comp.databases.oracle.server)