question

Hi @milind.jivtode_158531: This is not possible in Cassandra or any hashing based system/database. "field need to be used in where clause without using allow filtering" is only possible if the field is part of the primary key in the table.
Actually, when you insert a record in Cassandra it evaluates the hash key for it based on the primary keys designed during table creation (which can be used in where clause in cql queries). Hence changing one or more primary key mean inserting a new record in the database corresponding to a new hash value generated for it. Basically, this is not the right use case for which Cassandra has been designed.

If you have such use cases I would suggest not to use Cassandra. otherwise, This is a good article to start understanding data modelling basics in Cassandra.

@milind.jivtode_158531 The partition key uniquely identifies your records and where they are placed around the ring in the cluster. This means that they have to be globally unique in your cluster and changing their values doesn't make sense.

Consider this table of users which records their age, gender and favourite colour:

CREATE TABLE users (
    name text,
    age int,
    gender text,
    favourite_colour text
    PRIMARY KEY (name)
)

For simplicity, let's say it contains these records:

name	age	gender	favourite colour
Alice	20	F	red
Bob	33	M	orange
Carol	24	F	yellow

In the table, the partition key is the column name. It doesn't make sense to change a user's name because that's what identifies them in the table. If you need to change someone's name, you're really inserting a new record in the DB and need to delete the existing record.

I recommend that since you're new to Cassandra you should do the free courses at DataStax Academy, in particular the DS220 course on Data Modelling. This 9-minute video from DS220 on Partitioning and Storage Structure would help you understand the concepts in more detail. Cheers!

UPDATE - In your table of users where the primary key is (city, name), you won't be able to query (filter) based on favourite_colour. You can only specify columns in the WHERE clause if:

• it is part of the partition key
• it is a clustering column (but must preceded by other clustering columns as defined in the primary key)
• it is indexed (with CREATE INDEX)

If the primary key is (city, name, favourite_colour), you still will not be able to query for people from LA whose favourite colour is red because you must specify all columns in the primary key (as noted in the second bullet point above), i.e. you can't avoid "skipping" the name column.

The most important rule of data modelling is that you need to create a table for each application query. This means you need a [duplicate] table whose primary key is (city, favourite_colour) so you end up with partitions of cities which have clusters of colours of rows of people. Logically, it looks like this:

partition: 'LA'
- clustering column: 'red'
  - name: 'Alice', age: 20, gender: 'F'
  - name: 'Bob', age: 33, gender: 'M'
  - name: 'Carol', age 24, gender: 'F'

When you query this table with:

SELECT name, age, gender FROM users_by_city \
  WHERE city = 'LA' AND favourite_colour = 'red'

you get:

 name  | age | gender
-------+--------------
 Alice |  20 |      F
   Bob |  33 |      M
 Carol |  24 |      F

I'm going to predict that you'll have lots of follow-up questions so I'll reiterate again that it's very important that you at least have a look at some of the videos from the DS220 course I previously mentioned. It will explain a lot of what you don't understand right now being a new Cassandra user. Cheers!