Expected constraint in dynamodb to prevent duplicates

Question

I am new to DynamoDB and I am having trouble getting the following scenario to work in node.js and DynamoDB:

I want to insert a user in a user table but I don't want this to succeed if the user's email address already exists.

The following code is able to successfully insert a new user:

var item = {
    id: { S: uuid.v4()},
    email: { S: '[email protected]' }
};

dynamodb.putItem({
    TableName: 'user',
    Item: item,
    Expected: {
        email: { Exists: false }
    }
}, function (err, data) {
    if (err) {
        return callback(err);
    }
    callback();
});

I assumed the Expected value would prevent duplicate email addresses in the user's table by raising an ConditionalCheckFailedException error. This is not the case; the code above will insert duplicate emails consistently. I tried adding the 'id' as an additional Expected field but this didn't change the behavior.

I do get a ConditionalCheckFailedException when I modify the code so the ID is fixed rather than a generated UUID;

var item = {
    id: { S: 'Test' },
    email: { S: '[email protected]' }
};

I should explain, the 'id' is the hash key and 'email' is the range key.

This behavior led me to believe the constraints only work on the record specified with the hash key. If that were the case the AWS documentation would be very confusing indeed (I highlighted in bold what confuses me):

If Exists is false, Amazon DynamoDB assumes that the attribute value does not exist in the table. If in fact the value does not exist, then the assumption is valid and the operation succeeds. If the value is found, despite the assumption that it does not exist, the operation fails with a ConditionalCheckFailedException.

Assuming the documentation is correct I must be doing something wrong? Any help would be much appreciated!

EDIT 2013-11-09

Based on the comment below I decided to change tactics. I'm a bit reluctant to use a value that I need to reference in most other domain objects that isn't immutable. I now have an email address table that acts as a lookup / index table for user ID's. This allows me to have the user change his or her email address quite easily (I could even support multiple email addresses).

Thanks for the help!

iuri iuri · Accepted Answer · 2013-11-08T11:18:33

The items in a DynamoDB table with range key are the combination of hash key and range key, sort of a unique(id, email) in mysql. So in your first approach, when you insert a user who's email already exists in the table, you are creating a whole new item because the uuid is completely different:

1st item -> Hash key: 7f224b97-c144-4df2-bc3e-cfba69d5bc6e, Range key: [email protected]

2nd item -> Hash key: 34cc6d26-dce4-4eb4-afec-3a382d9579fc, Range key: [email protected]

So the Expected condition is working indeed, but no other item in the table with the Hash key 7f224b97-c144-4df2-bc3e-cfba69d5bc6e has a Range key = [email protected], and the put item succeeds.

If you really want to guarantee the uniqueness of the user's email, you should put it as the Hash key of the table. It will also make things easier to you when you want to query a user: as you probably know, to perform a query in DynamoDB you have to specify the exact value of the hash key you want to query, if you do not know the value of the hash key you are looking for, you will have to perform a table scan (much more inefficient).

So if you use an uuid as a Hash key, you will have to retrieve your users using a table scan (I assume that you don't know the uuid associated to a user you want to retrieve from DB). If you use the user mail as the Hash key, you will be able to retrieve them using queries.

Hope it helps!

Expected constraint in dynamodb to prevent duplicates

1 Answers