Unique index on a nullable field?

[Chris Hanks]

Hi -

I'm putting together a Mongo-backed Ruby app (using MongoMapper), and
have just spent the past few hours tracking down a really annoying
bug. The root cause is that I have unique indexes specified on a
couple of fields in one of my collections, but these fields are
allowed to be nil, and Mongo doesn't seem to work with this.

For example, in my users collection I have an email field. I don't
want to have two users with the same email address, so the email index
is set to be unique. At the same time, I allow users to browse and use
my app for a while without giving me their email address, so for many
of these user records the email value will simply be nil. But this
doesn't work with Mongo:

Mongo::OperationFailure: E11000 duplicate key errorindex: local-
development.users.$email_1 dup key: { : null }

Am I doing something wrong? If it's not possible to have a unique
index on a nullable field in Mongo (which would be a very frustrating
limitation), how are other people working around this?

Thanks

[Eliot Horowitz]

Null is a value so you can only have 1 entry with it. As far as I know
this is how all unique indexes work in all dbs. Your best bet is
probably to use a non unique index and check uniqueness yourself.

On Jan 5, 2010, at 2:26 AM, Chris Hanks
<christoph...@gmail.com> wrote:

> --
>
> You received this message because you are subscribed to the Google
> Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com
> .
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en
> .
>
>

[Michael Dirolf]

For more, see:
http://www.mongodb.org/display/DOCS/Indexes

[zahariash]

On 5 Sty, 15:04, Eliot Horowitz <eliothorow...@gmail.com> wrote:
> Null is a value so you can only have 1 entry with it. As far as I know  
> this is how all unique indexes work in all dbs.

In mysql you can do that:
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL auto_increment,
`u` varchar(5),
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index` (`u`)
);

insert into t(u) values('a');
//OK
insert into t(u) values('a');
//Duplicate entry 'a' for key 2

but

insert into t(u) values(null);
//OK
insert into t(u) values(null);
//OK

MyISAM and InnoDB engines works that way. It could be really useful.

[Valentin Golev]

Maybe, you can try using MongoID instead of null (if your field is not MongoID of itself). It's unique and requires just a simple check to determine if it's a "null" or sensible value
- Valentin Golev

[Keith Branton]

On Jan 5, 7:04 am, Eliot Horowitz <eliothorow...@gmail.com> wrote:
> As far as I know this is how all unique indexes work in all dbs.

Not in Oracle either.

In oracle null <> null - so unique indexes can contain any number of
nulls (unless they are composite - but that's a different story).

On the other hand, I would always do unique checking at the
application level as well - having a unique constraint imposed by the
database is a nice safety net though, in case there is a bug in the
application logic.

Basically mongo's unique indexes can't be used on polymorphic
collections unless every document has a value for the indexed field.
It wouldn't be so bad if it was just nulls that are actually sent to
the database - but documents with no value for an indexed field are
also treated as if the value were provided and set to null for the
purpose of indexing.

There is a jira ticket for this:

http://jira.mongodb.org/browse/SERVER-484

[Chris Hanks]

I'm used to using partial indexes in Postgres, where if an indexed
field is null, that row simply isn't included in the index. It also
makes looking up rows under those situations much faster, and keeps
the indexes small.

Thanks for your input, everyone.