CODEWITHSUNDEEP
cassandraunique-constraint
user3467525
user3467525

Reputation: 71

using OR in select query in cassandra

I want to make uniqueness for my data so I need to add OR clause in my WHERE clause.

ex: select * from table where id =123 OR name ='abs'

where id is partition key and name is clustering key.

so in above I want that I get data if my id is 123 or my name is abs .

is there any possibility in Cassandra to achieve this .

Upvotes: 2

Views: 291

Answers (1)

Andy Tolbert
Andy Tolbert

Reputation: 11638

This is not something you can do with cassandra as is, but there are patterns you can follow to achieve this.

The main reasoning this doesn't work is that since 'name' is not the partition key it would require a full data scan to find rows with name 'abs'.

You could make two queries by doing the following:

select * from table where id=123;
select * from table where name='abs' ALLOW FILTERING;

but I would not recommend using ALLOW FILTERING in your main application path. You could also add an index, but I would also not recommend this as name will have high cardinality given the assumption that name will be unique. Global index support coming in cassandra 3.0 may help that (CASSANDRA-6477).

I think a better route would be to consider adjusting or adding to your data model.

For example, since you are trying to enforce uniqueness on 'name', why not make that the primary key? Does id offer any significance for you? I would consider it an uncommon requirement to enforce uniqueness on two separate columns. Typically a primary key/uniqueness constraint comprises of a single column or a combination of columns used together, not exclusively.

Another thing you could consider is declaring a second table 'table_by_name' that acts as an index mapping name to id, using the following schema:

create table table_by_name (
  id int,
  name text,
  primary key (name, id)
);

You would then need to maintain changes to the data into both tables. However, you could now make two separate queries to determine if the record already exists:

select * from table where id=123;
select * from table_by_name where name='abs';

This would be a relatively inexpensive to do and the performance would be good. The only cost is that you are now duplicating your data, but this is effectively what an index will do in a relational database, storing the indexed column and included columns in a separate place.

Upvotes: 2

Related Questions