DynamoDB conditional check on Secondary Index does not prevent duplicates

Code examples are in Go but this is not a Go-specific question

I am trying to prevent duplicate writes on an existing partition key for a duplicate local secondary index. I have a Dynamo table (simplified) with these attributes:

| companyID (partition key) | createdAt_ms (sort key) | username (LSI)

The username field is a LocalSecondaryIndex - the name of which is username_index, it uses the same partition key, and the sort key is itself.

I'm trying to prevent PutItem requests into my table where the username is already present for a company ID. I've tried using the following as the conditional check to perform before writing

//tried with both "username_index" and "username"  condition := expression.NotEqual(expression.Key("username_index"), expression.Value("Nate123"))    req := &dynamodb.PutItemInput{              TableName:                 aws.String(db.Store.TableName),              ConditionExpression:       expr.Condition(),              ExpressionAttributeNames:  expr.Names(),              ExpressionAttributeValues: expr.Values(),              // ... etc          }  

This fails though if the sort key is different, e.g. if the table already has Google | 4560000 | Nate123 and I try to insert Google | 7890000 | Nate123, it will allow it on the above PutItem request. It does succeed in preventing a PutItem request however if the sort key is exactly the same.

I have also tried this:

// tried both "username" and "username_index"  condition := expression.AttributeNotExists(expression.Name("username_index"))  

And that didn't work in either scenario. I understand that the concept of the "primary key" in Dynamo is its partitionKey + sortKey if they are both present, but was wondering if the LSI thrown into the mix could help me out.

Basically, for a given partition key, is it possible to prevent a duplicate LSI write if the sort key is different, or do I need to change my schema so the username is the sort key?

https://stackoverflow.com/questions/67013333/dynamodb-conditional-check-on-secondary-index-does-not-prevent-duplicates April 09, 2021 at 07:58AM