Why does INSERT work without a clustering key?

Question

This question was closed Mar 13 at 09:55 PM by Erick Ramirez for the following reason:

question

cristiscu asked Mar 13, '21 Erick Ramirez answered Mar 13, '21

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

10 |1000 characters needed characters left characters exceeded

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

cristiscu · Mar 13 at 04:37 PM

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 ·

charmikhambhati · Mar 13 at 05:29 PM

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

1 ·

screenshot-20210313-225810-gallery.jpg (72.7 KiB)

Answer 1 · 2021-03-13T21:55:40Z

Erick Ramirez answered Mar 13, '21

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

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

question

Why does INSERT work without a clustering key?

1 Answer

question details

OTHER RESOURCES

question

Why does INSERT work without a clustering key?

1 Answer

question details

Related Questions

OTHER RESOURCES