Multi row uniqueness constraint

elandau edited this page May 22, 2012 · 4 revisions
Clone this wiki locally

The purpose of this recipe to optimize a uniqueness constraint check on multiple row keys in multiple column families within the same keyspace. The recipe works by first writing a unique column to ALL rows being checked. Each row is then read in sequence to verify if there is a collision. If no collisions are identified the unique lock columns are committed without a TTL or expiration date. Otherwise all columns are rolled back.

Column Family Settings

For this recipe to work the column family can have any key and default validation class but the comparator type must be UTF8Type. Also, it must be set up with replication factor 3, for quorum. Calls use QUORUM_LOCL but can be modified to use QUORUM_EACH.

Check uniqueness of multiple rows

For example, let's say you need to ensure that a both a username and email address are unique as part of a sign up operation.

MultiRowUniquenessConstraint unique = new MultiRowUniquenessConstraint(keyspace)
    .withRow(USERNAME_CF, "someusername")
    .withRow(EMAIL_CF, "")
    .withLockId("SomeUniqueStringSuchAsACustomerUuid");  // optional
try {
catch (NotUniqueException e) {
    // At least one of the row keys is already taken
catch (Exception e) {
    // Exception such as a general failure talking to cassandra

Reading the current lock id

A unique lock id is autogenerated or may be provided by the caller when the multi-row uniqueness constraint is set up. The lock id can be the row key where the actual data is stored. In the above example, email and username are made unique by the uniqueness constraint but the actual data will be stored elsewhere with row key 'SomeUniqueStringSuchAsACustomerUuid'. Since the lock columns contain a 'foreign key' to the actual user data it is possible for a user to log in using an email address or a username with one lookup to the lock column and then a query of the actual user data column family. The data would look like this,

UserNameColumnFamily : {
    "someusername" : {
         _LOCK_SomeUniqueStringSuchAsACustomerUuid = 0,

EmailColumnFamily : {
    "" : {
         _LOCK_SomeUniqueStringSuchAsACustomerUuid = 0,

UserDataColumnFamily : {
    "SomeUniqueStringSuchAsACustomerUuid" : {
         "email" : "",
         "username" : "someusername"

To look up the user id using username

ColumnPrefixUniquenessConstraint usernameLookup
    = new ColumnPrefixUniquenessConstraint(keyspace, USERNAME_CF, "someusername");
String userid = usernameLookup.readUniqueColumn();  // NOTE: readUniqueColumn will be renamed to readLockId in a future version of Astyanax