cassandra partition and clustering key

Question

Is is possible to have a column as a partition and clustering key? For example,

Create table citylist2 ( city varchar, loc list, pop int, zip varchar, state varchar, primary key (city,city,zip)) WITH CLUSTERING ORDER BY (city ASC, zip DESC);

results in:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Unknown definition city referenced in PRIMARY KEY"

I might be doing this wrong, but can anyone tell me if it is possible to have the column "city" as the partition and clustering key and how to do so if it is possible?

Answer 1

The issue is likely that you are trying to reference city twice in the Primary key definition.

Answer 2

As far as I understand, this is not possible. The partition key splits your data over partitions, and the cluster key will then sort the data within each partition. So it doesn't make sense to have a partition key which is also a clustering key. You may need to rethink your data model for what it is you are attempting.

Answer 3

Create table citylist2 ( city varchar,citycopy varchar, loc list, pop int, zip varchar, state varchar, primary key (city,citycopy,zip)) WITH CLUSTERING ORDER BY (citycopy ASC, zip DESC);

The above can be used if you really want to do what you are trying to do - by duplicating the same data in two columns.

If you can provide more details on why do you want to use the same data as partition and clustering, may be the answer will change.