CODEWITHSUNDEEP
cassandracql
user3790173
user3790173

Reputation: 45

Cassandra table with all fields as PK

I'm quite new to Cassandra and I'm trying to create some helper table in addition to already existed one (e.g. items with PK as item_id) to be able querying by different fields.

For example:

CREATE TABLE IF NOT EXISTS items_ids_by_field1_and_field2(
  item_id TEXT,
  field1  TEXT,
  field2  TEXT,

  PRIMARY KEY ((field1, field2), item_id));

INSERT works fine, but the problem comes when I want to execute UPDATE on this table, because all the fields are part of PK.

What could be a solution for this issue? Thanks.

Upvotes: 3

Views: 904

Answers (1)

Andy Tolbert
Andy Tolbert

Reputation: 11638

The reason why you cannot UPDATE a row where all columns are the primary key is because Cassandra does not let you change the value of a primary key, because you would in effect be referring to a different row completely as the primary key is how a row is uniquely identified.

If you want all columns to be part of the primary key and you want to change one of them, what you could do instead is delete the existing row first, and then insert the new data. Since you need all columns to make any changes (since they all create the primary key) using INSERT instead of UPDATE is fine:

DELETE FROM items_ids_by_field1_and_field2 where field1='X' and field2='Y' and item_id='Z'
INSERT INTO items_ids_by_field1-and_field2 (field1, field2, item_id) values ('X', 'Y', 'A')

Would accomplish what you need to do. If 'X', 'Y', 'Z' doesn't exist that isn't a problem for Cassandra.

Upvotes: 4

Related Questions