CODEWITHSUNDEEP
cassandradatastaxcqlshcassandra-3.0
Syed Ammar Mustafa
Syed Ammar Mustafa

Reputation: 413

Cassandra does not support DELETE on indexed columns

Say I have a cassandra table xyz with the following schema :

create table xyz(
xyzid uuid,
name text,
fileid int, 
sid    int,
PRIMARY KEY(xyzid));

I create index on columns fileid , sid:

CREATE INDEX file_index ON xyz (fileid);
CREATE INDEX sid_index ON xyz (sid);

I insert data :

INSERT INTO xyz (xyzid, name , fileid , sid ) VALUES ( now(), 'p120' , 1, 100);
INSERT INTO xyz (xyzid, name , fileid , ssid ) VALUES ( now(), 'p120' , 1, 101);
INSERT INTO xyz (xyzid, name , fileid , sid ) VALUES ( now(), 'p122' , 2, 101);

I want to delete data using the indexed columns :

 DELETE from xyz WHERE fileid=1 and sid=101;

Why do I get this error ?

InvalidRequest: code=2200 [Invalid query] message="Non PRIMARY KEY fileid found in where clause"

  1. Is it mandatory to specify the primary key in the where clause for delete queries ?

  2. Does Cassandra supports deletes using secondary index s ?

  3. What has to be done to delete data using secondary index s ?

  4. Any suggestions that could help .

I am using Data Stax Community Cassandra 2.1.8 but I also want to know whether delete using indexed columns is supported by Data Stax Community Cassandra 3.2.1

Thanks

Upvotes: 3

Views: 7481

Answers (2)

Mikhail Baksheev
Mikhail Baksheev

Reputation: 1414

I suppose you can perform delete in two steps:

  1. Select data by secondary index and get primary index column values (xyzid) from query result
  2. Perform delete by primary index values.

Upvotes: 2

bechbd
bechbd

Reputation: 6341

Let me try and answer your questions in order:

1) Yes, if you are going to use a where clause in a CQL statement then the PARTITION KEY must be an equality operator in the where clause. Other than that you are only allowed to filter on clustering columns specified in your primary key. (Unless you have a secondary index)

2) No it does not. See this post for some more information as it is essentially the same problem. Why can cassandra "select" on secondary key, but not update using secondary key? (1.2.8+)

3) Why not add sid as a clustering column in your primary key. This would allow you to do the delete or query using both as you have shown.

create table xyz( xyzid uuid, name text, fileid int, sid int, PRIMARY KEY(xyzid, sid));

4) In general using secondary indexes is considered an anti-pattern (a bit less so with SASI indexes in C* 3.4) so my question is can you add these fields as clustering columns to your primary key? How are you querying these secondary indexes?

Upvotes: 3

Related Questions