How to create a table with 2 primary keys and 0 clustering keys

ScyllaDB
1

Hello all,
In the tutorials provided in the scylla university, it says that it is possible to have one or more primary keys and 0 or more clustering keys.

In the example provided for compound keys in scylladb you can define a table like this:

CREATE TABLE heartrate_v1 (
   pet_chip_id uuid,
   time timestamp,
   heart_rate int,
   PRIMARY KEY (pet_chip_id, time)
);

In this example, it creates a table with a primary key with pet_chip_id and with a clustering key on time column.

I have two questions:

  1. How can I make time to be part of the primary key not clustering key?
  2. Why can I have two rows with the same pet_chip_id but different time? Since time is part of the clustering key and is only used for sorting, why is it also used for identifying rows like primary keys?

Thank you so much

2
  • How can I make time to be part of the primary key not clustering key?

Add one additional set of parenthesis, like this:

CREATE TABLE heartrate_v1 (
   pet_chip_id uuid,
   time timestamp,
   heart_rate int,
   PRIMARY KEY ((pet_chip_id, time))
);

In general, an extra set of parenthesis can be used to delineate the partition key from the clustering key: PRIMARY KEY ((partition_key_col1, partition_key_col2), clustering_key_col1, clustering_key_col2). The part in the inner parenthesis is the partition key.

1 Like
3
  • Why can I have two rows with the same pet_chip_id but different time? Since time is part of the clustering key and is only used for sorting, why is it also used for identifying rows like primary keys?

Clustering keys are used for more than just sorting, they identify rows, just like partition keys identify partitions. Clustering keys allow you to have many rows within the same partition. If you want a single row in a partition, don’t define a clustering key.

1 Like