Creating a table with a compound primary key

The syntax for creating tables with compound primary keys is a bit different from the single-column primary key syntax we saw in the previous chapter. We create a user_status_updates table with a compound primary key, as follows:

CREATE TABLE "user_status_updates" (
  "username" text,
  "id" timeuuid,
  "body" text,
  PRIMARY KEY ("username", "id")
);

In particular, instead of appending a single column definition with a PRIMARY KEY modifier, we make a separate PRIMARY KEY declaration at the end of the list of columns, which itself specifies the two columns that make up the compound primary key. While this is the only way to declare a compound primary key, it's also a perfectly valid way to declare a single-column primary key. So, our users table from the previous chapter could have been declared like this:

CREATE TABLE "users" (
  "username" text,
  "email" text,
  "encrypted_password" blob,
  PRIMARY KEY ("username")
);

Here, we simply move the PRIMARY KEY declaration to the end of the column list. It's a little less concise, but it has the exact same effect as a single column definition with a PRIMARY KEY modifier.

The structure of the status updates table

The most notable aspect of user_status_updates is that it has two columns for a primary key. This means that each row is identified uniquely by the combination of its username and id columns. It also means that every row must have a value in both of these columns.

In addition to this, our user_status_updates table is the first time we've seen a timeuuid column in the wild. As you can recollect from the previous chapter, a UUID is essentially a very large number that is generated using an algorithm that guarantees that the identifier is unique across time and space.

You will also recollect that Cassandra does not have the ability to generate auto-incrementing sequences for use in primary keys, as this would require an unacceptably high level of coordination between nodes in the cluster. In the users table, we used a natural key; we want each user to have a unique username, so the username column makes a perfectly good unique identifier for rows.

In the case of a status update, however, there is no obvious natural key. The only user-generated data associated with a status update is the body, but a free text field doesn't make a very good primary key, and anyway there's no guarantee that status update bodies will be unique. This is where UUIDs come in handy. Since they're guaranteed to be unique, we can use them as a surrogate key—a unique identifier that isn't derived from the data in the row. Auto-incrementing primary keys in relational databases are also surrogate keys.