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

cristiscu avatar image
cristiscu asked Erick Ramirez answered

Why does INSERT work without a clustering key?

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

INSERT INTO tutorialkeyspace.countries (country, total_pop)
   VALUES ('USA', 328000000);

SELECT * FROM tutorialkeyspace.countries;

According to the spec, the INSERT should fail. But it works fine in Amazon Keyspaces (using Apache Cassandra 3.11.2). And DataStax Astra (DSE DB 4.0.0.687).

I suspect it has something to do with the STATIC field. Without it, it doesn't work indeed.

clustering key
2 comments
10 |1000 characters needed characters left characters exceeded

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

Another example, for DSE 6.0, from here:

INSERT INTO cycling.cyclist_expenses (
    cyclist_name, balance
  ) VALUES (
    'Vera ADRIAN', 0
  ) IF NOT EXISTS;

This should rather fail, because expense_id is a clustering key, and your doc on INSERT says:

"[INSERT] Inserts an entire row or upserts data into an existing row; statement must include the full Primary_key. Requires a value for each component of the primary key, but not for any other columns."

I think you made it possible at some point to insert STATIC fields without passing any cluster keys (which makes sense), but the documentation has not been updated...

1 Like 1 ·

screenshot-20210313-225810-gallery.jpg

To add to that, I actually tried doing it both the ways and observed the following behaviours.

1 Like 1 ·

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered

It is only necessary to specify the partition key when inserting/updating the value of total_pop since it is a STATIC column. I've previously explained this in questions #9755 and #10735.

The reason is that a static column has the same value for all rows in the partition hence the term "static". It doesn't matter which row in the partition you are modifying -- the value is the same for all rows so the clustering key is not required. Cheers!

Share
10 |1000 characters needed characters left characters exceeded

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