Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

guzelcihad avatar image
guzelcihad asked Erick Ramirez commented

How do you determine the partition key in the PRIMARY KEY definition?

Hi guys,

I am stucked with the primary key definition.

So far, I've seen definition like this:

CREATE TABLE countries (
  country text,
  total_pop int STATIC,
  state text,
  state_pop int,
  PRIMARY KEY ((country), state)
);

and this way I understand that country is the partition key and state is the clustering key.

But I've seen an example like this:

CREATE TABLE countries (
  country text,
  total_pop int STATIC,
  state text,
  state_pop int,
  PRIMARY KEY (country, state)
);

And when I print description of this table, it says:

CREATE TABLE killr_video.countries (
    country text,
    state text,
    total_pop int static,
    state_pop int,
    PRIMARY KEY (country, state)
) WITH CLUSTERING ORDER BY (state ASC)

state is the clustering key. How can I figure it out with this type of definition which column is which?

Can anyone explain it to me?

data modeling
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez commented

I see where the confusion is. For all intents and purposes, these definitions are identical:

  PRIMARY KEY ((country), state)
  PRIMARY KEY (country, state)

In the first example, the country column is enclosed in brackets for effect only to highlight that it is the partition key -- it's a valid CQL syntax but is unnecessary.

In both examples, country is the partition key. This is because the partition key is always the first component of the PRIMARY KEY. Notice I used the word "component" instead of column.

In your table, the partition key is composed of just a single column. But in tables where the partition is composed of 2 or more columns, you must explicitly enclose the composite partition key in brackets. The brackets enforce that the combination of columns is the first component of the PRIMARY KEY and is therefore the partition key.

Here's an example table that has a composite partition key:

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".

If you're interested, I've explained simple primary key, composite partition key, and compound primary key in question #6171. Cheers!

2 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

guzelcihad avatar image guzelcihad ·

Appreciated

0 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ guzelcihad ·

Not a problem. Cheers!

0 Likes 0 ·