question

Definitions

A table's primary key is one or more columns that uniquely identify:

1. the location of the data in a cluster of nodes, and
2. the order of the stored data.

The primary key's first element is the partition key. It is used to determine which node holds a given table's row(s) by hashing its value into a partition token (done by the default Murmur3Partitioner which uses the MurmurHash algorithm).

A simple primary key uses just one column as the partition key. When there are 2 or more columns enclosed in parenthesis at the start of a primary key, it is known as a composite partition key.

For tables with a compound primary key, the primary key has both a partition key and one or more clustering columns.

Examples

SIMPLE PRIMARY KEY

In this table, there is only one column in the partition key (username).

CREATE TABLE users (
    username text,
    realname text,
    email text,
    PRIMARY KEY (username)
)

COMPOSITE PARTITION KEY

In this table, there are 2 columns that form the partition key enclosed in parenthesis ((title, year)):

CREATE TABLE videos (
    title text,
    year int,
    description text,
    ...
    PRIMARY KEY ((title, year))
)

The video title on its own is not unique. For example, the 1950 release of Superman with Kirk Alyn in the leading role is not the same Superman movie released in 1978 starring Christopher Reeve so we need to append the year next to the title to make the partition key unique -- "Superman:1950" and "Superman:1978".

COMPOUND PRIMARY KEY

This table has a single-column partition key (userid) and a clustering column (email):

CREATE TABLE user_emails (
    username text,
    email_type text,
    email_address text
    ...
    PRIMARY KEY (userid, email_type)
)

A user can have multiple emails -- personal, work, etc.

This table has a composite partition key ((title, year)) and 2 clustering columns (commented_at and comment):

CREATE TABLE comments_by_video_title (
    title text,
    year int,
    commented_at timestamp,
    comment text,
    username text,
    PRIMARY KEY ((title, year), commented_at, comment)
) WITH CLUSTERING ORDER BY (commented_at DESC)

Comments are sorted with most recent as the first row. In this case, we can retrieve the 10 most recent comments about a video with the following query:

SELECT comment FROM comments_by_video_title \
    WHERE title = 'Superman'
    AND year = 1978;

Cheers!