Row uniqueness in Cassandra table with PRIMARY KEY (hash, created)

Question

I'm trying to create a table containing 2 columns, one of which is a unique hash and another is the timestamp of when the row was created.

CREATE TABLE IF NOT EXISTS keyspace.table (
    account varchar,
    hash varchar,
    lt bigint,
    created timestamp,
    PRIMARY KEY (hash, created)
)  WITH CLUSTERING ORDER BY (created DESC);

I want the table to be ordered by the created field but unique on only the hash field. I believe the uniqueness is currently checked as a union of both the fields; and a new row is only considered a duplicate if it has the same value for both of the fields.

Answer 1

What you're after is not possible.

To give you a bit of background, each hash (partition key) uniquely identifies each partition in the table. Since your schema has a compound primary key (partition key + clustering key), each partition in your table has one or more rows of created). The whole primary key therefore identifies each unique row in the partition.

It isn't possible at all for just the partition key hash to be the unique identifier for the a row. If that's what you want, you need to model your data differently with:

CREATE TABLE accounts_by_hash (
    ...
    PRIMARY KEY (hash)
)

Ultimately, you need to go back to the primary principle of data modelling in Cassandra and that is you need to design tables based on the application queries. Cheers!