what is the reason for composite column, there must be at least one column which is not part of the primary key

Question

From online document: A CQL 3 table’s primary key can have any number (1 or more) of component columns, but there must be at least one column which is not part of the primary key.

What is the reason for that?

I tried to insert a row only with the columns in the composite key in CQL. I can't see it when I do SELECT

cqlsh:demo> CREATE TABLE DEMO ( 
user_id bigint,
    dep_id bigint,
    created timestamp,
    lastupdated timestamp,
    PRIMARY KEY (user_id, dep_id)
     );

cqlsh:demo> INSERT INTO DEMO (user_id, dep_id)
     ...   VALUES (100, 1);

cqlsh:demo> select * from demo;


 cqlsh:demo>

But when I use cli, it shows up something:

default@demo] list demo;
Using default limit of 100
Using default column limit of 100
-------------------
RowKey: 100

1 Row Returned.
Elapsed time: 27 msec(s).

But can't see the values of the columns.

After I add the column which is not in the primary key, the value shows up in CQL

 cqlsh:demo> INSERT INTO DEMO (user_id, dep_id, created)
         ...   VALUES (100, 1, '7943-07-23');
  cqlsh:demo> select * from demo;
  user_id | dep_id | created                  | lastupdated
  ---------+--------+--------------------------+-------------
   100 |      1 | 7943-07-23 00:00:00+0000 |        null

Result from CLI:

 [default@demo] list demo;
 Using default limit of 100
 Using default column limit of 100
  -------------------
 RowKey: 100
 invalid UTF8 bytes 0000ab7240ab7580
 [default@demo]

Any idea?
update: I found the reason why CLI returns invalid UTF8 bytes 0000ab7240ab7580, it's not compatible with the table created for from CQL3, if I use compact storage option, the value shows up correctly for CLI.

After Dig a bit more, the row is indeed added, but if I use SELECT * FROM tableName, it didn't return unless I added a column which is not part of the primary key. — Wei Zhu

Chris Gerken Chris Gerken · Accepted Answer · 2012-10-29T21:49:39

What's really happening under the covers is that the non-key values are being saved using the primary key values which make up the row key and column names. If you don't insert any non-key values then you're not really creating any new column family columns. The row key comes from the first primary key, so that's why Cassandra was able to create a new row for you, even though no columns were created with it.

what is the reason for composite column, there must be at least one column which is not part of the primary key

2 Answers