Unique index
> db.users.createIndex({"name":1},{"unique":true})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 4,
"numIndexesAfter" : 5,
"ok" : 1
}
> db.users.insertOne({"name":"user164"})
WriteError({
"index" : 0,
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.users index: name_1 dup key: { name: \"user164\" }",
"op" : {
"_id" : ObjectId("5f7c208d02c41bd39db9af73"),
"name" : "user164"
}
}) :
WriteError({
"index" : 0,
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.users index: name_1 dup key: { name: \"user164\" }",
"op" : {
"_id" : ObjectId("5f7c208d02c41bd39db9af73"),
"name" : "user164"
}
})
WriteError@src/mongo/shell/bulk_api.js:458:48
mergeBatchResults@src/mongo/shell/bulk_api.js:855:49
executeBatch@src/mongo/shell/bulk_api.js:919:13
Bulk/this.execute@src/mongo/shell/bulk_api.js:1163:21
DBCollection.prototype.insertOne@src/mongo/shell/crud_api.js:264:9
@(shell):1:1
>
partialFilterExpression
MongoDB treat null as the same value in index, can use partialFilterExpression to filter only specific field exist
// re-create unique index with partialFilterExpression
> db.users.dropIndex({"name":1})
{ "nIndexesWas" : 5, "ok" : 1 }
> db.users.createIndex({"name":1},{"unique":true,"partialFilterExpression":{"comments":{$exists: true}}})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 4,
"numIndexesAfter" : 5,
"ok" : 1
}
// insert duplicated name will success
> db.users.insertOne({"name":"user164"})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f7c234602c41bd39db9af76")
}
> db.users.insertOne({"name":"user164"})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f7c234c02c41bd39db9af77")
}
// however, if insert user with comments field, unique index works
> db.users.insertOne({"name":"user164","comments":"abc"})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f7c23f702c41bd39db9af78")
}
> db.users.insertOne({"name":"user164","comments":"abcdef"})
WriteError({
"index" : 0,
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.users index: name_1 dup key: { name: \"user164\" }",
"op" : {
"_id" : ObjectId("5f7c23fa02c41bd39db9af79"),
"name" : "user164",
"comments" : "abcdef"
}
}) :
WriteError({
"index" : 0,
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.users index: name_1 dup key: { name: \"user164\" }",
"op" : {
"_id" : ObjectId("5f7c23fa02c41bd39db9af79"),
"name" : "user164",
"comments" : "abcdef"
}
})
WriteError@src/mongo/shell/bulk_api.js:458:48
mergeBatchResults@src/mongo/shell/bulk_api.js:855:49
executeBatch@src/mongo/shell/bulk_api.js:919:13
Bulk/this.execute@src/mongo/shell/bulk_api.js:1163:21
DBCollection.prototype.insertOne@src/mongo/shell/crud_api.js:264:9
@(shell):1:1
>
Compound unique indexes
// create compound unique index
> db.tickets.createIndex({"name":1,"location":1},{"unique":true})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
// insert the first record
> db.tickets.insertOne({"name":"T1","location":"Taipei"})
{
"acknowledged" : true,
"insertedId" : ObjectId("5f7c408f4db79b5cc8c5423d")
}
// insert the second record fail
> db.tickets.insertOne({"name":"T1","location":"Taipei"})
WriteError({
"index" : 0,
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.tickets index: name_1_location_1 dup key: { name: \"T1\", location: \"Taipei\" }",
"op" : {
"_id" : ObjectId("5f7c40904db79b5cc8c5423e"),
"name" : "T1",
"location" : "Taipei"
}
}) :
WriteError({
"index" : 0,
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.tickets index: name_1_location_1 dup key: { name: \"T1\", location: \"Taipei\" }",
"op" : {
"_id" : ObjectId("5f7c40904db79b5cc8c5423e"),
"name" : "T1",
"location" : "Taipei"
}
})
WriteError@src/mongo/shell/bulk_api.js:458:48
mergeBatchResults@src/mongo/shell/bulk_api.js:855:49
executeBatch@src/mongo/shell/bulk_api.js:919:13
Bulk/this.execute@src/mongo/shell/bulk_api.js:1163:21
DBCollection.prototype.insertOne@src/mongo/shell/crud_api.js:264:9
@(shell):1:1
>
沒有留言:
張貼留言