I've to map two distinct set of unique ids. Example:

Now need to ensure that everything in the ID column is unique, and everything in the Ext ID column. I also need fast lookup based on ID and Ext ID.
If I make ID my parition key it's solved for ID. But Ext ID is an issue. Global Secondary Index do not enforce uniqueness, so no solution for Ext ID.

How can I ensure that ID and Ext ID remains unique while still providing fast lookup on both? A dummy entry for id and always maintain two entries upon insert and delete?

CodePudding user response：

If you would want to ensure there's only ever one unique value for both the ID and ExtId, one option could be using conditonal writes when you perform Put or UpdateItem operations.

You could specify the attribute_not_exists(ExtId) as your condition and it would reject any update/create of an item, if there's already an item with the same attribute value you specify as ExtId.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ConditionExpressions.html

But, just verifying it wouldn't suffice to you if you just defined your table with composite primary key, leveraging ID as partition and ExtId as sort key? It leaves the possibility though that you could have the same extid on multiple partitions, but in that case you could use the same conditional writes to cover them.

CodePudding user response：

You can write two items using transactions, one as ID/ExtID and the other ExtID/ID. Name your attributes as PK and SK so there’s no assumption of what ID either is because it’s going to vary.

Make each write conditional on the item not already existing. If either write fails the other is failed as well.

If there’s a chance of an ID and ExtID having the same value then prepend ID# or ExtID# depending, to disambiguate.