E11000 on field that is not duplicated

I’m trying to create a unique complex index. [(‘COMPANY_NAME’, -1), (‘user_name’, 1)].
I’m getting a 11000 error:
dup key: { COMPANY_NAME: null, user_name: null } and it’s referring to a document that doesn’t have that feiald!
What can be the reason for that?
Thank you

Non existing fields default to null in terms of index value.

You may use a partial index to explicit ignore null or non existing fields.

1 Like

Hi @Moshe_G welcome to the community!

@steevej is correct. For completeness, here’s the relevant snippet regarding this in the Unique Index page:

If a document does not have a value for the indexed field in a unique index, the index will store a null value for this document. Because of the unique constraint, MongoDB will only permit one document that lacks the indexed field.

Best regards

1 Like

Thank you all for the helpful answer. Based on your response, I added {sparse=True} and it works just fine!

@steevej, I see the “partial index” is better than {sparse=True}…