5

I have a cassandra table who has a composite primary key: (school_id, student_id). Let's say that I want to delete all the records in this table that belong to one school. Using cassandra driver, I tried to bind only the school_id like:

val query = QueryBuilder.delete().all().from(session.loggedKeyspace, "mytable")
   .where(QueryBuilder.eq("school_id", QueryBuilder.bindMarker())
   .bind("school_1")
session.execute(query)

I get an error saying that:

com.datastax.driver.core.exceptions.InvalidQueryException: Missing mandatory PRIMARY KEY part student_id

I could have thousands of students in one school. Do I have to query the table first to get all distinct student_ids and then use this delete statement?

breezymri
  • 3,975
  • 8
  • 31
  • 65

3 Answers3

3

Yes, you will have to provide all the components of your primary key to successfully execute the delete statement. You will have to query your table to get all the records you want to delete and then execute the delete statement by passing both school_id and student_id

reference : DELETE | CQL for Cassandra 3.0

Hitesh
  • 3,449
  • 8
  • 39
  • 57
0

You can also drop the table, and then recreate it. This will clean your table of every record: https://docs.datastax.com/en/cql/3.1/cql/cql_reference/drop_table_r.html

Pedro Gordo
  • 1,825
  • 3
  • 21
  • 45
0

Yes, as Hitesh mentioned, you have to specify all fields of your partition key.

If you can influence the table design, it is probably a better choice to have school_id as partition key, and student_id as clustering key:

CREATE TABLE myTable (
  school_id bigint,
  student_id bigint,
  -- other fields,
  PRIMARY KEY ((school_id), student_id)
)

In this case you can filter by just the school_id but also by school_id, student_id pairs.

medvekoma
  • 1,179
  • 7
  • 11