ScyllaDB: Prevent duplicate entries on specific columns

Question

I'm using ScyllaDB and I have a table with the following 5 columns:

K1 K2 V1 V2 V3

Where K1 is the partition key, K2 is the clustering key, V1..V3 are three columns representing 3 different values in the table.

I want to prevent duplicate values from being added to the table where K1, K2, V1 and V2 match an existing entry in table. In other words, it should not be possible to add/store more than one row where ALL 4 columns in this row match an existing row with the same values.

Is this possible to achieve with Scylla?

Thanks

Answer 1

The most reliable way to achieve that is to make all 4 of those columns be part of the primary key of the table. Keys are naturally de-duplicated or better said, a new write with a key value will just overwrite the old value with said key. You mention that the current schema is something like this (assuming text as type for simplicity):

CREATE TABLE ks.tbl (
    K1 text,
    K2 text,
    V1 text,
    V2 text,
    V3 text,
    PRIMARY KEY(K1, K2)
);

You can change your primary key to be like this: PRIMARY KEY(K1, (K2, V1, V2)). You will still be able to query based on just K1 and K2, as clustering restrictions allow for only a prefix of the clustering key to be specified.