DynamoDBSaveExpression with conditional check on GSI

Question

I want to make an update but the conditional check I want to add is not based on the Hash/Range of the primary table but of a GSI.

Effectively, I want to fail the save if a given attribute (i.e. GSI's hash) already exists.

As an example, in an imaginary employees table, “SSN” is the hash key and there is a GSI on “EmployeeId”. Both of these attributes need to be unique all around. While saving an employee, I want to make sure that neither of “SSN” or “EmployeeId” is already in use in the table. I can do it for Hash of the table i.e. SSN but not for Hash of the GSI.

Is it supported? I did not see that in the documentation.

Thanks!

Answer 1

The method you have described is not possible using DynamoDB even with the newly announced transactions. The reason is because your GSI is eventually consistent and may not reflect the most up-to-date state of the items in your table, so no ConditionExpression can be used on a GSI.

However, you can achieve similar functionality by having a separate EmployeeId-InUse table. It could be as simple as a single attribute: employeeId. Since this is a table rather than a GSI, you can use a TransactWriteItems request to write to your EmployeeData and your EmployeeId-InUse tables at the same time. A transaction will fail if any part of the transaction fails, so you can use a ConditionExpression to Prevent Overwrites of an Existing Item to ensure the transaction will fail if the SSN or the EmployeeId already exists.

Here’s a sample of what the “TransactItems” part of your dynamodb request would look like for this.

“TransactItems”: [
    {
        “Put” : {
            “ConditionExpression” : “attribute_not_exists(ssn)”,
            “Item” : {
                ... employee data goes here ...
             },
            “TableName”: “EmployeeData”
        }
    },
    {
        “Put” : {
            “ConditionExpression” : “attribute_not_exists(employeeId)”,
            “Item” : {
                “employeeId”: {
                      “S” : “Employee1457”
                }
            },
            “TableName”: “EmployeeIDs-InUse”
        }
    }
]

There are a couple of things you need to watch out for here. Make sure you update the EmployeeId-InUse table every time you add or remove an employee and also if you update the employeeId that is associated with a given SSN. Also, be aware that a transaction will consume twice as much capacity as a regular write.