Search Restrictions Solution: ALLOW FILTERING option

In Apache Cassandra, the table’s primary key imposes restrictions on search conditions, impacting the design of the relational model. In this lesson, we will focus on these limitations and their solutions.

To reiterate, in a SELECT query:

• Only the columns that are part of the primary key can be used in the WHERE clause as filtering conditions.

• Only equality searches can be performed on partition key columns. Range queries are allowed on clustering columns.

• If the partition key is composed of multiple columns (composite partition key), all partition key columns must be included in the WHERE clause. 

• Clustering columns may not be included in the search condition. To include a clustering column in the WHERE clause, all clustering columns defined before it in the primary key must also be included in the search condition.

ALLOW FILTERING option of the SELECT statement

To rephrase the above restrictions, tables in Apache Cassandra cannot be queried using:

1. Non-primary key column(s)

2. Non-partition key column(s)

3. Part of the composite partition key

To elaborate, we will use the following table.