Simulating Amazon DynamoDB unique constraints using transactions

Most relational database systems—and some non-relational database systems—have a construct known as a unique key or a unique constraint. This feature ensures that all values in a column or field are unique across rows.

For example, if you have a User table, you might have a UUID as a primary key that uniquely identifies each user, but you might also have user name and email fields (“attributes” in DynamoDB terminology), which also must be unique for that user. This use case was mentioned in the AWS Summit 2018 DAT374 Session on DynamoDB Transactions.

In Amazon DynamoDB, the primary key is either the partition key (if no sort key is chosen for the table), or the combination of the partition and sort key. Primary keys are guaranteed to be unique within a table. However, DynamoDB does not have a built-in mechanism for ensuring the uniqueness of attributes that are not the primary key.

This post describes a pattern that is used to implement this kind of uniqueness from the application side. We show you examples of how to create, update, and delete items when using this pattern in a single-table schema design.

Solution overview

Using the preceding example, imagine that you have a User table and that table has attributes like the following:

• pk (primary key stored as a UUID)
• userName
• email
• fullName
• phoneNumber

The UUID, userName, and email attributes must be unique, but fullName and phoneNumber do not. Multiple people might share the same home phone number. Some samples rows follow.

Because DynamoDB already guarantees that the pk attribute is unique, you need a mechanism to ensure that the userName and email attributes are also unique.

To do this, insert extra items into the same table, with the pk attribute set to the attribute name and value from the item, delimited by a hash sign. The new table looks like the following example:

Every time that you insert a new item into the table, you also must insert the other two items. This guarantees uniqueness for the userName and email attributes. Similarly, if you delete an item, you must delete the other two corresponding items.

Finally, if you modify one of the unique attributes (such as in the case where the user wants to change their email address on their account), you must update the related unique items as well. All of these modifications should be bounded together in a DynamoDB Transaction so that either they all succeed or fail together.

Command-line examples

Next, examine some command-line examples for implementing this design. These CLI examples can be easily ported to any of the DynamoDB SDKs for your preferred programming language.

Imagine that you have a user table and it’s empty. Register your first user by inserting their three rows as part of a transaction and use a transaction identifier to ensure idempotency. This transaction identifier (the client request token) allows you to submit an identical transaction more than one time, perhaps in the case of a restarted or resumed application, and still end up with the same result.

aws dynamodb transact-write-items --client-request-token TRANSACTION1 --transact-items '[
  {
    "Put": {
      "TableName" : "User", 
      "ConditionExpression": "attribute_not_exists(pk)",
        "Item" : {
          "pk":{"S":"b201c1f2-238e-461f-88e6-0e606fbc3c51"},
          "userName":{"S":"btables"},
          "email":{"S":"bobby.tables@gmail.com"},
          "fullName":{"S":"Bobby Tables"},
          "phoneNumber":{"S":"+1-202-555-0124"}
       }
    }
},
  {
    "Put": {
      "TableName" : "User", 
      "ConditionExpression": "attribute_not_exists(pk)",
      "Item" : {
        "pk":{"S":"userName#btables"}
       }
    }
},
  {
    "Put": {
      "TableName" : "User", 
      "ConditionExpression": "attribute_not_exists(pk)",
      "Item" : {
        "pk":{"S":"email#bobby.tables@gmail.com"}
       }
    }
}
]'

Now, if you list the items, you can see that all three items have been created.

aws dynamodb scan --table-name User
{
    "Count": 3,
    "Items": [
        {
            "userName": {
                "S": "btables"
            },
            "pk": {
                "S": "b201c1f2-238e-461f-88e6-0e606fbc3c51"
            },
            "fullName": {
                "S": "Bobby Tables"
            },
            "phoneNumber": {
                "S": "+1-202-555-0124"
            },
            "email": {
                "S": "bobby.tables@gmail.com"
            }
        },
        {
            "pk": {
                "S": "email#bobby.tables@gmail.com"
            }
        },
        {
            "pk": {
                "S": "userName#btables"
            }
        }
    ],
    "ScannedCount": 3,
    "ConsumedCapacity": null
}

Following is what happens if a phony Bobby Tables tries to sign up using the same email address.

aws dynamodb transact-write-items --client-request-token TRANSACTION2 --transact-items '[
   {
     "Put": {
       "TableName" : "User",
       "ConditionExpression": "attribute_not_exists(pk)",
         "Item" : {
           "pk":{"S":"8ec436a8-97e6-4e72-aec2-b47668e96a94"},
           "userName":{"S":"caulfield"},
           "email":{"S":"bobby.tables@gmail.com"},
           "fullName":{"S":"Phony Bobby Tables"},
           "phoneNumber":{"S":"+1-202-555-0124"}
        }
     }
 },
   {
     "Put": {
       "TableName" : "User",
       "ConditionExpression": "attribute_not_exists(pk)",
       "Item" : {
         "pk":{"S":"userName#caulfield"}
        }
     }
 },
   {
     "Put": {
       "TableName" : "User",
       "ConditionExpression": "attribute_not_exists(pk)",
       "Item" : {
         "pk":{"S":"email#bobby.tables@gmail.com"}
        }
     }
  }
]'

An error occurred (TransactionCanceledException) when calling the TransactWriteItems operation: Transaction cancelled, please refer cancellation reasons for specific reasons [None, None, ConditionalCheckFailed]

As you can see, this transaction fails because the third element in the transaction received a ConditionalCheckFailed error as there was already an item existing with the pk value of “email#bobby.tables@gmail.com”.

If little Bobby Tables wants to register a vanity domain and change his stored email address, you must only modify two items. But because DynamoDB doesn’t allow you to modify the primary key of an item, you must delete the unique email item and insert another item with the new email.

aws dynamodb transact-write-items --client-request-token TRANSACTION3 --transact-items '[
  {
    "Update": {
      "TableName" : "User",
      "Key" : {"pk":{"S":"b201c1f2-238e-461f-88e6-0e606fbc3c51"}},
      "UpdateExpression":"SET email = :email",
      "ExpressionAttributeValues":{":email":{"S":"bobby@tables.com"}}
    }
  },
    {
    "Delete": {
      "TableName" : "User",
      "Key" : {"pk":{"S":"email#bobby.tables@gmail.com"}}
    }
  },
  {
    "Put": {
      "TableName" : "User",
      "ConditionExpression": "attribute_not_exists(pk)",
      "Item" : {
        "pk":{"S":"email#bobby@tables.com"}
       }
    }
}
]'

A scan shows that the intended changes were made.

aws dynamodb scan --table-name User
{
    "Count": 3,
    "Items": [
        {
            "userName": {
                "S": "btables"
            },
            "pk": {
                "S": "b201c1f2-238e-461f-88e6-0e606fbc3c51"
            },
            "fullName": {
                "S": "Bobby Tables"
            },
            "phoneNumber": {
                "S": "+1-202-555-0124"
            },
            "email": {
                "S": "bobby@tables.com"
            }
        },
        {
            "pk": {
                "S": "userName#btables"
            }
        },
        {
            "pk": {
                "S": "email#bobby@tables.com"
            }
        }
    ],
    "ScannedCount": 3,
    "ConsumedCapacity": null
}

Similarly, when Bobby wants to delete himself from the internet, you must remove all three rows related to his user.

aws dynamodb transact-write-items --client-request-token TRANSACTION4 --transact-items '[
 {
   "Delete": {
     "TableName" : "User",
     "Key" : {"pk":{"S":"b201c1f2-238e-461f-88e6-0e606fbc3c51"}},
     "ConditionExpression": "attribute_exists(pk)"
   }
 },
 {
   "Delete": {
     "TableName" : "User",
     "Key" : {"pk":{"S":"userName#btables"}},
     "ConditionExpression": "attribute_exists(pk)"
   }
 },
 {
   "Delete": {
     "TableName" : "User",
     "Key" : {"pk":{"S":"email#bobby@tables.com"}},
     "ConditionExpression": "attribute_exists(pk)"
   }
 }
]'

A final scan shows that the table is now empty:

aws dynamodb scan --table-name User
{
    "Count": 0,
    "Items": [],
    "ScannedCount": 0,
    "ConsumedCapacity": null
}

Conclusion

This pattern may be useful to you if you are migrating from relational databases and must maintain uniqueness constraints in DynamoDB. Because a single transaction can modify more than one table, it is possible to implement this same pattern using two tables. Keep your “main data” in the User table and have a second table, which is used purely for the purpose of ensuring uniqueness on certain attributes.

We try to maintain the single table design concept wherever possible in DynamoDB, but if using two tables for this pattern makes you feel more comfortable, go for it!

About the Authors

Chad Tindel is a DynamoDB Specialist Solutions Architect based out of New York City. He works with large enterprises to evaluate, design, and deploy DynamoDB-based solutions. Prior to joining Amazon he held similar roles at Red Hat, Cloudera, MongoDB, and Elastic.

Brett Hensley is part of the AWS Solutions Architecture team focusing on SLG which includes state, city, and county level government entities along with GovTechs, the various government technology partners. Prior to joining AWS, Brett served in a number of technical roles at companies such as Kimball Electronics, Maxim Group, BMG Columbia House, and Hearst Health subsidiary fdb (First DataBank). Having worked in the industry over 20 years supporting manufacturing, retail, healthcare, and government, Brett serves to assist customers in navigating the various technical, cultural, and procedural challenges in order to better serve their businesses, customers, or constituents.