How can I have null column value for a composite key column in CQL3

Easility picture Easility · Sep 23, 2013 · Viewed 12.8k times · Source

This may sound silly as there are no null values in SQL's composite primary key. But just want to confirm if we can have the same in CQL3?
So, we have a table like this to store wide rows:

CREATE TABLE keyspace12.colFamily1 
(
  id text, 
  colname text,
  colvalue blob,
  PRIMARY KEY (id,colname, colvalue)
) WITH COMPACT STORAGE

And we have some cases where colname is null. Can I do that? If yes, then how? If NO, then what are the ways to store wide columns rows where we can have some null in first part of composite column of cassandra(As per Thrift's convention)?

The related questions are: CQL3 and millions of columns composite key use case and Cassandra -How to create composite column name (not key) using cqlsh

Answer

abhi picture abhi · Sep 24, 2013

Say i have a column family

CREATE TABLE cnt_test (time_slot text , comp1 text, comp2 text, field1 counter, field2 counter, PRIMARY KEY(time_slot,comp1, comp2));

Now i am trying to insert some data in it

UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='' AND comp2='ABC';
UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='' AND comp2='ABC';
UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='' AND comp2='ABC';
UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='' AND comp2='XYZ';
UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='PQR' AND comp2='';

As you can see in the above statements i have inserted some empty values in the compound key part, just instead of null i am putting the blank character.

I can even query on the same

 SELECT * FROM cnt_test WHERE time_slot='20130924' AND comp1='' AND comp2='ABC';

 time_slot | comp1 | comp2 | field1 | field2
-----------+-------+-------+--------+--------
  20130924 |       |   ABC |      4 |      8

(1 rows)

SELECT * FROM cnt_test WHERE time_slot='20130924' AND comp1='PQR' AND comp2='';

 time_slot | comp1 | comp2 | field1 | field2
-----------+-------+-------+--------+--------
  20130924 |   PQR |       |      1 |      2

(1 rows)

So to summarize every thing just replace the null column value with empty column i.e ''