Enforcing Unique Constraint in GAE

Answer 1

This and other similar questions come up often when talking about transitioning from a traditional RDB to a BigTable-like datastore like App Engine's.

It's often useful to discuss why the datastore doesn't support unique keys, since it informs the mindset you should be in when thinking about your data storage schemes. The reason unique constraints are not available is because it greatly limits scalability. Like you've said, enforcing the constraint means checking all other entities for that property. Whether you do it manually in your code or the datastore does it automatically behind the scenes, it still needs to happen, and that means lower performance. Some optimizations can be made, but it still needs to happen in one way or another.

The answer to your question is, really think about why you need that unique constraint.

Secondly, remember that keys do exist in the datastore, and are a great way of enforcing a simple unique constraint.

my_user = MyUser(key_name=users.get_current_user().email())
my_user.put()

This will guarantee that no MyUser will ever be created with that email ever again, and you can also quickly retrieve the MyUser with that email:

my_user = MyUser.get(users.get_current_user().email())

In the python runtime you can also do:

my_user = MyUser.get_or_create(key_name=users.get_current_user().email())

Which will insert or retrieve the user with that email.

Anything more complex than that will not be scalable though. So really think about whether you need that property to be globally unique, or if there are ways you can remove the need for that unique constraint. Often times you'll find with some small workarounds you didn't need that property to be unique after all.

Answer 2

You can generate unique serial numbers for your products without needing to enforce unique IDs or querying the entire set of entities to find out what the largest serial number currently is. You can use transactions and a singleton entity to generate the 'next' serial number. Because the operation occurs inside a transaction, you can be sure that no two products will ever get the same serial number.

This approach will, however, be a potential performance chokepoint and limit your application's scalability. If it is the case that the creation of new serial numbers does not happen so often that you get contention, it may work for you.

EDIT: To clarify, the singleton that holds the current -- or next -- serial number that is to be assigned is completely independent of any entities that actually have serial numbers assigned to them. They do not need to be all be a part of an entity group. You could have entities from multiple models using the same mechanism to get a new, unique serial number.

I don't remember Java well enough to provide sample code, and my Python example might be meaningless to you, but here's pseudo-code to illustrate the idea:

1. Receive request to create a new inventory item.
2. Enter transaction.
3. Retrieve current value of the single entity of the SerialNumber model.
4. Increment value and write it to the database
5. Return value as you exit transaction.

Now, the code that does all the work of actually creating the inventory item and storing it along with its new serial number DOES NOT need to run in a transaction.

Caveat: as I stated above, this could be a major performance bottleneck, as only one serial number can be created at any one time. However, it does provide you with the certainty that the serial number that you just generated is unique and not in-use.