PRIMARY KEY definition in Cassandra 1.2 using CQL 3

Question

I am newbie in Cassandra and following this Cassandra tutorial on youtube. In this tutorial, author is creating two tables as shown below:

create table children(childid varchar, firstname varchar, lastname varchar, country varchar, state varchar, zip varchar, primary key(child)) with compact storage;

create table naughtyornicelist(standingbycountry varchar, state varchar, zip varchar, childid varchar, primary key(standingbycountry, state, zip, childid));

I am facing two problems now.

First problem is that children table gets created only when I remove with compact storage.

Second problem is that while creating naughtyornicelist table, Cassandra is throwing following error : Bad Request: No definition found that is not part of the PRIMARY KEY

I gave following command

cqlsh:northpole> create table naughtyornicelist(standingbycountry varchar, state varchar, zip varchar, childid varchar, primary key(standingbycountry, state, zip, childid));
Bad Request: No definition found that is not part of the PRIMARY KEY
cqlsh:northpole>

I am not able to find why this error is coming and how to resolve this error. Request Cassandra people to please help.

Lyuben Todorov · Answer 1 · 2013-08-28T12:03:54.177

children should fail during creation regardless of compact storage or not, your primary key is not defined:

create table northpole.children(
  childid varchar,         <---- column name = childid
  firstname varchar, 
  lastname varchar, 
  country varchar, 
  state varchar, 
  zip varchar, 
  primary key(child)       <---- column name = child != childid
) with compact storage;

No definition found that is not part of the PRIMARY KEY

You are creating a column family (cql table) that uses every column (cql talk) in the primary key. You cant do that. If you want to be able to use columns in the WHERE clause consider secondary indexed:

create table northpole.naughtyornicelist (
  standingbycountry varchar, 
  state varchar, 
  zip varchar, 
  childid varchar, 
  PRIMARY KEY(childid, standingbycountry)
);

CREATE INDEX inex_name ON northpole.naughtyornicelist (zip);
CREATE INDEX inex_name ON northpole.naughtyornicelist (state);

What you have as the primary key is quite important as it dictates the distribution and clustering of data. The first part of the key (in this case childid) is called the partition key and is used to distribute data across different nodes so you want your partitioning key to be as random as possible.The rest of the fields make up the clustering key which is used for ordering so that querying columns with the same clustering key can be more efficient. Note this is a rough guide, I don't know how you will be querying, but you will have to adjust your schema accordingly.

PRIMARY KEY definition in Cassandra 1.2 using CQL 3

1 Answers1