CODEWITHSUNDEEP
transactionscassandraunique-constraint
Vivin Paliath
Vivin Paliath

Reputation: 95508

Enforcing uniqueness using lightweight transactions in Cassandra

This is more of a data-modeling question, but it is related to lightweight transactions because it appears that trying to use this feature is having an impact on how I model my data.

My specific use-case is that I want to enforce uniqueness on specific fields on INSERT. Cassandra's documentation has the following example:

INSERT INTO customer_account (customerID, customer_email) 
VALUES (‘LauraS’, ‘[email protected]’)
IF NOT EXISTS;

In my particular case, I have the following table:

CREATE TABLE IF NOT EXISTS plugins (
    id uuid PRIMARY KEY,
    user_id uuid,
    name text,
    slug text,
    major_version int,
    minor_version int,
    patch_version int
);

The constraint I want to enforce is that the combination of user_id, slug, major_version, minor_version, and patch_version needs to be unique. I cannot simply do:

INSERT INTO 
    plugins (
        user_id, 
        slug, 
        major_version, 
        minor_version, 
        patch_version
    ) VALUES (...) IF NOT EXISTS;

This is because I need name, and id. However, including name, and id will still cause things to be written in because IF NOT EXISTS will fail due to the fact that the id will be different (at the very least), and name may be different as well.

Does this mean that I need to maintain a separate table that only has user_id, slug, major_version, minor_version, and patch_version? I would then attempt to write to this table, and look at the result of the operation. If the write succeeds, I will go ahead and populate all the other associated tables.

I know that denormalization is a fact of life with Cassandra; I just want to make sure that it makes sense here to create this table just to handle the case of uniqueness. If this approach doesn't make sense, please do suggest another one. Thanks!

Upvotes: 0

Views: 131

Answers (1)

Roman Tumaykin
Roman Tumaykin

Reputation: 1931

INSERT ... IF NOT EXISTS creates a new row if no row with provided key exists. It does not compare column by column, only the key. If you want to enforce the uniqueness of user_id, slug, major_version, minor_version and patch_version then those columns need to form a primary key.

Upvotes: 3

Related Questions