CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodb
NateH06
NateH06

Reputation: 3574

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?

Upvotes: 3

Views: 1385

Answers (1)

Jason Wadsworth
Jason Wadsworth

Reputation: 8887

This is a common misunderstanding of what a condition expression does in DynamoDB. A condition expression only checks to see if the record you are updating meets the condition. It does not look to see if there is another record that meets that condition. If you need to be sure a value is really only one way to be certain of that; use the unique value as the key of the record, or some part of it that will allow it to remain unique.

You can use a transaction to do a check for the existence of an item, including checking for certain values of that item, that doesn't have the be the item you are adding/updating, but I don't believe there is an option to say "do this if that item does NOT exist". Even if there is, the unique value you are talking about would have to be part of the key (the condition check on a transaction requires the key of the item, it's not a query).

Upvotes: 2

Related Questions