CODEWITHSUNDEEP
cassandrakotlincomposite-primary-key
breezymri
breezymri

Reputation: 4353

cassandra: delete all records that match one entry of composite primary key

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?

Upvotes: 5

Views: 2968

Answers (2)

medvekoma
medvekoma

Reputation: 1181

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.

Upvotes: 0

Hitesh
Hitesh

Reputation: 3498

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

Upvotes: 3

Related Questions