Cassandra does not support DELETE on indexed columns

Question

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"

Is it mandatory to specify the primary key in the where clause for delete queries ?
Does Cassandra supports deletes using secondary index s ?
What has to be done to delete data using secondary index s ?
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

How often are you really querying only by PRIMARY KEY? If those indexes are so important for you to have, why don't you create a query table that uses those indexed columns as PRIMARY KEY components? — Aaron

bechbd bechbd · Accepted Answer · 2016-03-21T12:37:17

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?

Cassandra does not support DELETE on indexed columns

2 Answers