Modelling Unique constraints in Cassandra

Question

I am a newbie to Cassandra. I would like to know how to model a unique constraint scenario in Cassandra.

E.g. I have a users table with the columns username, email, First Name. Since no 2 users should be allowed to have the same username nor should they be allowed to have the same email id, how do I model this?

In traditional RDMS terms, I would create Unique Keys on each of the columns.

Thanks

Answer 1

You'll have to create an additional table for the username and email with both using the value as the primary key. Remember that primary keys will always be unique.

Answer 2

You create a primary key composed by both user and email

CREATE TABLE users (
  username text,
  email text,
  age int,
  someotherdata text,
  PRIMARY KEY ((username, email))
)

In this way you're saying there will always be 1 username identified by both username and email. This is a little "frustrating" because you won't be able to get users information by only knowing username, you need to know both username and email

Another solution could be using a clustering key, static columns and a secondary index

CREATE TABLE users (
  username text,
  email text,
  age int static,
  someotherdata text static,
  PRIMARY KEY (username, email)
)

CREATE INDEX ON users (email);

The second solution allows what follows

1 - An user can have more emails associated
2 - The secondary index on email allow you to check, before writing, if a mail is not already used by someone else
3 - You can get user information by only knowing its username OR email

And using static columns you will have only one age/someotherdata/whatever for user.

Pay attention to the parenthesis on the primary key, in example one I'm saying the partition key is the one made by both username and email, in example two I'm saying the partition key is username and the clustering key is email.

HTH, Carlo