#Is there a way to enforce unique constraint in convex db?

4 messages · Page 1 of 1 (latest)

karmic gorge
#

In the same way that SQL allow to add

CONSTRAINT unique_pairs (id_a, id_b)

vestal lynxBOT
#

Hi @karmic gorge. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

#

Yes, Convex provides a way to enforce unique constraints, although it's implemented differently from SQL's CONSTRAINT clause. In Convex, you can achieve this using the unique property when defining fields in your schema.

Here's how you can enforce uniqueness in Convex:

  1. For single fields:
    You can make a field unique by adding the unique: true property when defining it in your schema. This is similar to creating a UNIQUE constraint on a single column in SQL.

    users: defineEnt({}),
  .field("email", { unique: true })

    Convex Ents: Manage your document relationships (https://stack.convex.dev/ents#unique-field-values)

  2. For compound uniqueness (similar to your SQL example):
    While Convex doesn't have a direct equivalent to SQL's compound UNIQUE constraint, you can achieve the same effect by creating an index on the fields you want to be unique together, and then checking for uniqueness in your mutation functions.

    Here's an example of how you might implement this:

    // In your schema:
myTable: defineTable({
  id_a: v.string(),
  id_b: v.string(),
}).index("by_id_a_and_id_b", ["id_a", "id_b"])

// In your mutation function:
const existing = await ctx.db
  .query("myTable")
  .withIndex("by_id_a_and_id_b", (q) => 
    q.eq("id_a", id_a).eq("id_b", id_b)
  )
  .unique();

if (existing !== null) {
  throw new Error("Duplicate entry");
}

// If no existing entry, proceed with insert

    Convex Ents: Manage your document relationships (https://stack.convex.dev/ents#unique-field-values)

This approach ensures that all writes to the table will check for uniqueness before inserting or updating, effectively enforcing a unique constraint on the combination of id_a and id_b.