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

cristiscu avatar image cristiscu commented ·

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 ·
charmikhambhati avatar image charmikhambhati commented ·

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 ·
screenshot-20210313-225810-gallery.jpg (72.7 KiB)

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