0

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?

Improve this question
2
  • 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.
    – jerichorivera
    Commented May 15 at 22:32
  • 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.
    – prasad_
    Commented May 16 at 3:42

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.