1

I have a collection test1 with this schema : 

{
 "_id" : ObjectId("576a5306177a78b5c286c95d"),
 "name" : "abdul",
 "lname" : "sayed"
},
{
 "_id" : ObjectId("576a5306177a78b5c286c95d"),
 "name" : "abcd",
 "lname" : "port"
}

I want to create a unique case-insensitive index on this collection for columns name & lname :

For the current eg :

1) "Abdul" & "Sayed" cannnot be added. (which is correct)

2) "Abdul" & "pqrs" should get added. However I am unable to add so.

It is giving me this error.

db.test1.insert({"name":"Abdul","lname":"pqrs"})

WriteResult({ "nInserted" : 1 })

db.test1.insert({"name":"Abdul","lname":"pqrs"}) WriteResult({ "nInserted" : 0, "writeError" : { "code" : 11000, "errmsg" : "E11000 duplicate key error collection: mydb.test1 index: name_text_lname_text dup key: { : \"abdul\", : 1.1 }" } })

What I Had tried : 

db.test1.createIndex({"name":"text","lname":"text"},{unique:true,"caseInsensitive":true})

Edit : List of Indexes :

> db.test1.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "mydb.test1"
        },
        {
                "v" : 1,
                "unique" : true,
                "key" : {
                        "_fts" : "text",
                        "_ftsx" : 1
                },
                "name" : "name_text_lname_text",
                "ns" : "mydb.test1",
                "caseInsensitive" : true,
                "weights" : {
                        "lname" : 1,
                        "name" : 1
                },
                "default_language" : "english",
                "language_override" : "language",
                "textIndexVersion" : 3
        }
]
>
Abdul Rehman Sayed
  • 6,532
  • 7
  • 45
  • 74
  • Can [this question](http://stackoverflow.com/questions/12573753/creating-multifield-indexes-in-mongoose-mongodb) help you understanding your problem? – Shrabanee Jun 22 '16 at 09:28
  • Console output pasted here shows, record with `db.test1.insert({"name":"Abdul","lname":"pqrs"}) WriteResult({ "nInserted" : 1 })` is inserted and for the next time it is saying duplicate, which is correct. What is the issue you are facing – Clement Amarnath Jun 22 '16 at 09:48
  • can you show a list of indexes on the collection? – Daniele Graziani Jun 22 '16 at 10:18
  • @Tiramisu please check the edit – Abdul Rehman Sayed Jun 22 '16 at 11:27
  • @ClementAmarnath It did not insert the record at all. Even I had got confused with the result – Abdul Rehman Sayed Jun 22 '16 at 11:28
  • @Shrabanee : My index needs to be case insensitive & unique. – Abdul Rehman Sayed Jun 22 '16 at 11:29
  • You got that error because you were duplicating: _fts, and _ftsx--the keys in your index by leaving them blank. try adding: db.test1.createIndex({"name":1, "lname":1}, {unique: true}); also drop your text index and recreate it without enforcing uniqueness. – Daniele Graziani Jun 22 '16 at 18:29
  • @Tiramisu but that wont create a case insensitive unique index for me..I need a unique index which is case insensitive. – Abdul Rehman Sayed Jun 23 '16 at 04:15
  • Hi @AbdulRehmanSayed, I ran into this one again and to make an index case insensitive you need to specify a collation in the index. Specify that collation when you create the collection and when you query it. – Daniele Graziani Mar 07 '17 at 22:32

0 Answers0