Cassandra insert value disappear

Question

I want to use the Cassandra database system to create tables. The original data is in the picture.

So I create these tables and insert the value

Create table course(
    Course_ID text PRIMARY KEY,
    Course_Name text,
    student_id text

);

However when I want to select all the student id from course American History :select * from course where Course_Name = 'Biology';

Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

Then when I try to print out all the table, I found all the value with some part of duplicate value is missing... Is it because of the way I design table is wrong? How can I change it and select all the student id from one course? Thanks!!

To frame Carlos' answer in a different light, why would you designate `course_id` as a `PRIMARY KEY` when you're not planning on querying by it? — Aaron, Apr 23 '19 at 16:07

score 2 · Accepted Answer · answered Apr 23 '19 at 14:58

The issue is that your query for the table course is not using the primary key; unlike relational databases, the tables in Cassandra are designed based on the query that you are going to execute, in this case, you can include the course name as part of the composite key:

Create table course(
  Course_ID text,
  Course_Name text,
  student_id text,
  PRIMARY KEY (Course_Name, Course_ID)
);

There are already answers explaining the difference between the keys like this one, you may also want to read this article from Datastax

Cassandra insert value disappear

1 Answers1