How to Create a Case-Insensitive Unique Compound Index in MongoDB?

Question

How to Create a Case-Insensitive Unique Compound Index in MongoDB?

Question Body: I'm trying to create a compound unique text index in MongoDB with the following fields:

tenant_id: 1 seller_id: 1 seller_sku_code: text. However, I'm encountering an error when trying to create the index:

Index build failed: 4df81a22-042f-4e51-bbec-b0f8a7dafe2e:
Collection product_db.skus ( 83cc8978-b0fe-4c4b-ae58-691e32db7f95 )
:: caused by :: E11000 duplicate key error
collection: product_db.skus index: tenant_id_1_seller_id_1_seller_sku_code_text dup key: { tenant_id: "1", seller_id: "113", _fts: "acc", _ftsx: 0.75 }

I need to ensure that the seller_sku_code is unique within the database and is case-insensitive. For example:

"ABC" and "abc" should not be allowed simultaneously. "ACC-2001" and "ACC-2000" should be allowed to coexist.

How can I create this unique, case-insensitive index on seller_sku_code while still ensuring the uniqueness of the tenant_id and seller_id fields?

• jerichorivera: Specify collation locale and strength refer to mongodb.com/docs/v5.0/core/index-case-insensitive. If you are using an old MongoDB version you might want to check if these are supported.
• prasad_: In a compound index uniqueness is applied on a combination of all fields in the index. If you need uniqueness on a specific field create a unique index on that field. Note that a field can be specified in more than one index. Can you include the MongoDB version, the code you are using to create the index and sample documents in your question.

Answer 1

If I am understanding your situation correctly, you are actually trying to achieve 2 individual requirements:

1. seller_sku_code: unique and case insensitive
2. tenant_id and seller_id: unique

If it is true, it could be easier to just set up 2 separate indices for them:

"indexes": [
 {
 "name": "tenant_id_seller_id_idx",
 "key": {
 "tenant_id": 1,
 "seller_id": 1
 },
 "unique": true
 },
 {
 "name": "seller_sku_code_idx",
 "key": {
 "seller_sku_code": 1
 },
 "unique": true,
 "collation": {
 "locale": "en",
 "strength": 2
 }
 }
 ]

Mongo Playground