Priority for checking a unique index

Working with Data
1

I have two unique indexes in the collection: for example, an index on resourceId and an index on idempotencyKey.

db.example.createIndex({resourceID: 1}, {unique: 1});
db.example.createIndex({idempotencyKey: 1}, {unique: 1});
db.example.insertOne({"resourceID": "some UUID", "idempotencyKey": "idempotencyUUID"});
// Add more
db.example.insertOne({"resourceID": "some UUID", "idempotencyKey": "idempotencyUUID"});

I get error like: E11000 duplicate key error collection: db.example index: resourceID_idx dup key: { resourceID: "someUUID" }

When executing the second query, both indexes are violated. But what if, for the logic of the program, it is necessary that checking the uniqueness of one index would be a priority? For example, idempotencyKey_idx instead resourceID_idx .

How to solve this problem?

2

You need a compound index.

3

compound index is not suitable, since we have to maintain uniqueness for each attribute

4

Then, I do not understand why the 2 indices is an issue.

If you insert a duplicate resourceID it fails.
If you insert a duplicate idempotencyKey it fails.

The only issue I can see is that if you insert a document that have both duplicate resourceID and duplicate idempotencyKey, you get a E11000 on only one. I would say that this is a nice optimization offered by the server. It tells you it fails on the first index fail rather than trying all the possible indices and reporting on all the failures.

If it is really important to know if they other also fails, you might always query the server if a document exists with the potential duplicate that is not reported.

Personally, I would not want to incur the cost of testing all the unique indices that fails. Most of the time, knowing that 1 condition fails is sufficient.

2 Likes
5

Thanks for the discussion!

I agree that in case of checking all indexes during data insertion it is not optimal.

But the fact is that in my case, if the uniqueness of different indexes is violated, I need to generate different errors for the system. And in the case when there is a simultaneous violation of two indexes, I need to give priority to an error for one of them.

Getting information about uniqueness using a query is a bad idea, since the state of the database may change between such a request and a data insertion request.

I see a possible solution to pre-insert in one transaction into a separate collection of the index key, which is more priority.

For example:

db.example.createIndex({resourceID: 1}, {unique: 1});
db.example.createIndex({idempotencyKey: 1}, {unique: 1});
db.example_idempotency.createIndex({idempotencyKey: 1});
db.example.insertOne({"resourceID": "some UUID", "idempotencyKey": "idempotencyUUID"});
// Add more
// BEGIN TRANSACTION
db.example_idempotency.insertOne({"idempotencyKey": "idempotencyUUID"}); // additional priority check
db.example.insertOne({"resourceID": "some UUID", "idempotencyKey": "idempotencyUUID"});
// END TRANSACTION

In this case I can control uniqueness with priority.
The solution doesn’t look super-good, but I haven’t found another one.

1 Like
6

Like you wrote

I’ll follow up if I think of something.

1 Like