Grid Comments - Answer Overflow

Grid

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

I thought that Drizzle would be able to create unique indexes since it's pretty close to what SQL is (from my understanding) by doing something similar to this:

CREATE UNIQUE INDEX unique_constraint_name ON subgroups (group_id, slug) WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX unique_constraint_name ON subgroups (group_id, slug) WHERE deleted_at IS NULL;

But I'll go with what you suggested!

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

okay, I'll try that

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

(thanks for the follow on Twitter btw!)

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

so after doing what you taught me yesterday, I thought of adding soft-deletion to my table definition (deleteAt field which is null by default), but that means that in my constraint I have to add the deletedAt field, but only if it's null.

export const subgroups = mysqlTable(
  "subgroups",
  {
    id: serial("id").primaryKey(),
    groupId: varchar("group_id", { length: 256 }).notNull(),
    slug: varchar("slug", { length: 32 }).notNull(),
    deletedAt: timestamp("deleted_at"),
  },
  (t) => ({
    subgroupIdSlugUniqueConstraint: unique().on(t.groupId, t.slug, t.deletedAt),
  })
);

export const subgroups = mysqlTable(
  "subgroups",
  {
    id: serial("id").primaryKey(),
    groupId: varchar("group_id", { length: 256 }).notNull(),
    slug: varchar("slug", { length: 32 }).notNull(),
    deletedAt: timestamp("deleted_at"),
  },
  (t) => ({
    subgroupIdSlugUniqueConstraint: unique().on(t.groupId, t.slug, t.deletedAt),
  })
);

This is what I currently have but I think this might not work because the constraint is not evaluating the deletedAt field only when it's null, it's evaluating the field whether it's null or if it has defined value

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

hey @T3NED, I'm back to haunt & bother you... 😅

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

will do!

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

last question (sorry @T3NED): in practice if at some point in the future I want to speed up the finding process of a row using the unique index you wrote above how should I select my row to successfully "hit"/"use" the index, making the process faster? Like this ? db.select({slug: 'abc', groupId: '789' }).from(subgroups)

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

thanks again for the thorough explanation

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

got it!

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

I don't think I'll use an index then, this is probably for business-sized solutions

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

thanks, and sorry for the stupid questions

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

got it!

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

So using an index would make finding by slug or groupId a subgroup a faster process?

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

I'm checking SQL docs to understand

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

So in this case, I could use the groupIdSlugUniqueIndex field to get the subgroup I want, but with unique I won't be able to? Sorry if I'm saying wrong stuff

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

Does this mean that unique is lighter in terms of performance, while uniqueIndex is a heavier hit but makes it easier to retrieve a subgroup based on said index?

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

Oh right, sorry the SQL world is something I'm pretty new into

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

One quick question, why should I use uniqueIndex() instead of unique()?

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

Oh my god I've been reading through this part twice without realizing this was the solution, thanks @T3NED

40 replies

Gaming

Programming