PRIMARY KEY definition in Cassandra 1.2 using CQL 3 -

i newbie in cassandra , following this cassandra tutorial on youtube. in tutorial, author creating 2 tables shown below:

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

i facing 2 problems now.

first problem children table gets created when remove with compact storage.

second problem while creating naughtyornicelist table, cassandra throwing following error : bad request: no definition found not part of 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 not part of primary key cqlsh:northpole> 

i not able find why error coming , how resolve error. request cassandra people please help.

children should fail during creation regardless of compact storage or not, primary key 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 ) compact storage; 

no definition found not part of primary key

you creating column family (cql table) uses every column (cql talk) in primary key. cant that. if want able use columns in 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 have primary key quite important dictates distribution , clustering of data. first part of key (in case childid) called partition key , used distribute data across different nodes want partitioning key random possible.the rest of fields make clustering key used ordering querying columns same clustering key can more efficient. note rough guide, don't know how querying, have adjust schema accordingly.