Nulls in a Unique Index
Wes Clark
are allowed up to one null. In H2, nulls seem to be treated a unique
values. Example:
create table t5(c1 char(1), c2 char(2));
create unique index i5 on t5(c1, c2);
insert into t5 values ('a', null);
insert into t5 values ('a', null);
The second insert succeeds on H2, and fails, as it should, on Oracle
and SQL Server.
Please reply to all, since I rarely check my Google email.
Thomas Mueller
If this fails for Oracle and MS SQL Server, then those databases are
not standards compliant in this regard by default. The ANSI SQL-92
standard says that NULL is not equal to NULL, that means the two rows
are not considered equal. I know this is a strange rule, and I don't
like it myself, but this is what is defined by the standard.
According to my tests, it works for MySQL, PostgreSQL, and H2. It
fails for Derby, HSQLDB, Oracle.
drop table t5;
create table t5(c1 char(1), c2 char(2));
create unique index i5 on t5(c1, c2);
insert into t5 values ('a', null);
insert into t5 values ('a', null);
I think that MS SQL Server has an option 'ANSI_NULLS' to achieve
standards compliance in this regard. See
http://weblogs.sqlteam.com/davidm/archive/2004/05/21/1364.aspx
What I could do is add a compatibility option for Derby, HSQLDB and
Oracle. However I like to avoid that if possible. Do you need this
feature?
Regards,
Thomas
Wes Clark
developer at Sybase and elsewhere responded:
Quote ------
ANSI does not define the semantics of indexes, but the applicable
behavior for nulls is DISTINCT and primary and unique constraints, not
=. Only 1 null value will be returned:
create table trash(c1 int);
insert into trash values(null);
insert into trash values(null);
select distinct c1 from trash;
truncate table trash;
alter table trash add unique (c1);
insert into trash values(null);
insert into trash values(null);
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__trash__26268016'. Cannot
insert duplicate key in object 'dbo.trash'.
The statement has been terminated.
Derby definitely has the correct ANSI semantics, because we used the
ANSI document and the VP of engineering had been on the ANSI committee
for a number of years.
------End Quote
Guidewire is an ISV, and supports Oracle and SQL Server in production,
and H2 for internal and customer development.
Another example:
create table t5 (c1 char(1), c2 char(1), c3 char(1));
create unique index i5 on t5 (c1, c2, c3);
insert into t5 values ('a', 'b', null);
insert into t5 values ('a', 'c', null); -- succeeds
insert into t5 values ('a', 'c', null); -- fails on Oracle and SQL
Server, succeeds on H2
The use case we plan on adding in our next release, which will require
SQL Server 2008, is the single column nullable unique index which can
support multiple nulls using a filtered index:
create table t6 (c1 char(1))
go
CREATE UNIQUE NONCLUSTERED INDEX i6
ON t6(c1)
WHERE c1 IS NOT NULL
go
insert into t6 values ('a')
insert into t6 values (null)
insert into t6 values (null) -- fails on SQL Server 2005 with
unfiltered index, succeeds on SQL Server 2008 with filtered index
go
select distinct c1 from t6
go
[returns two rows]
This works the same on Oracle without additional syntax.
A compatibility option, at a minimum, that would enable H2 to work as
Oracle and SQL Server with filtered indexes do, that is, a unique
index insures uniqueness of all non-null values, would be of great
benefit.
On Jun 11, 11:23 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
>
> If this fails for Oracle and MS SQL Server, then those databases are
> not standards compliant in this regard by default. The ANSI SQL-92
> standard says that NULL is not equal to NULL, that means the two rows
> are not considered equal. I know this is a strange rule, and I don't
> like it myself, but this is what is defined by the standard.
>
> According to my tests, it works for MySQL, PostgreSQL, and H2. It
> fails for Derby, HSQLDB, Oracle.
>
> drop table t5;
> create table t5(c1 char(1), c2 char(2));
> create unique index i5 on t5(c1, c2);
> insert into t5 values ('a', null);
> insert into t5 values ('a', null);
>
> I think that MS SQL Server has an option 'ANSI_NULLS' to achieve
> What I could do is add a compatibility option for Derby, HSQLDB and
> Oracle. However I like to avoid that if possible. Do you need this
> feature?
>
> Regards,
> Thomas
>
Thomas Mueller
> Jerry Brenner (jbre...@guidewire.com), a past database internals
> developer at Sybase and elsewhere responded:
> ANSI does not define the semantics of indexes, but the applicable
> behavior for nulls is DISTINCT and primary and unique constraints, not
> =
Well, let's just say NULL behavior is complicated and not well
defined. It doesn't really matter who is 'right' and who is 'wrong'
;-) The main problem is that databases behave differently. For H2 I
picked what I thought is the more 'standards compliant' way. I'm not
sure if that was the right decision if it turns out that only MySQL
and PostgreSQL support multiple rows with NULL.
It would be much simpler if NULL is considered equal to NULL, smaller
than any other value, and if there was no ternary logic
(http://en.wikipedia.org/wiki/Ternary_logic). But that's just a
fantasy ;-)
> Derby definitely has the correct ANSI semantics, because we used the
> ANSI document and the VP of engineering had been on the ANSI committee
> for a number of years.
Hehe ;-) I was working at PointBase with Steve Jones, he was also on
the ANSI committee. As far as I remember, he said the exact opposite
;-)
> A compatibility option, at a minimum, that would enable H2 to work as
> Oracle and SQL Server with filtered indexes do, that is, a unique
> index insures uniqueness of all non-null values, would be of great
> benefit.
Sure, I agree. I will add a new compatibility flag
'uniqueIndexNullDistinct' in the next release to only allow one row
with 'NULL' in a unique index. This flag will be enabled for Derby,
Oracle, MSSQLServer, and HSQLDB (but not for Regular, MySQL, and
PostgreSQL.
Regards,
Thomas
Wes Clark
with this feature. I will download it and test it.
Wes Clark
compatibility mode should allow
drop table t1;
create table t1 (c1 char(1));
create unique index i1 on t1(c1);
insert into t1 values (null);
insert into t1 values (null);
In Oracle, in a single column unique index, multiple nulls are
allowed, while in SQL Server, only one null is allowed.
Similarly, in this sequence
drop table t2;
create table t2 (c1 char(1), c2 char(1));
create unique index t2i1 on t2(c1, c2);
insert into t2 values (null, null);
insert into t2 values (null, null);
the second insert will succeed in Oracle and fail in SQL Server.
So I would suggest you could change the MODE=Oracle behavior to match
Oracle in this case.
Wes Clark
and Program Files folder is named H2 1.0.71 instead of H2 1.0.74.
Thomas Mueller
> BTW, when I installed the latest download for Windows, the shortcuts
> and Program Files folder is named H2 1.0.71 instead of H2 1.0.74.
Could the reason be that you have renamed it from just "H2" to "H2
1.0.71" when you installed it the first time? The default is "H2". It
will probably use the same name if you install it over an older
version.
Thomas Mueller
So far I thought Oracle doesn't allow multiple NULL when using a
unique constraint or index. I have tested it now and it works. In the
next release, I will fix the Oracle mode.
Thanks,
Thomas