Grid Posts - Answer Overflow

Grid

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

Unique constraint on multiple fields/columns

Hey guys, I'm getting my hands on Drizzle for the first time and I was wondering if/how, with the ORM, I can create a unique index based on two fields:

export const subgroups = mysqlTable("subgroup", {
  id: serial("id").primaryKey(),
  groupId: text("groupId").notNull(),
  slug: text("slug").notNull(), // I'd like this to be unique, but not in the entire table, only unique within the subgroups related to `groupId`
});

export const subgroupsRelations = relations(
  subgroups,
  ({ one, many }) => ({
    group: one(groups, {
      fields: [subgroups.groupId],
      references: [groups.id],
    }),
  })
);

export const subgroups = mysqlTable("subgroup", {
  id: serial("id").primaryKey(),
  groupId: text("groupId").notNull(),
  slug: text("slug").notNull(), // I'd like this to be unique, but not in the entire table, only unique within the subgroups related to `groupId`
});

export const subgroupsRelations = relations(
  subgroups,
  ({ one, many }) => ({
    group: one(groups, {
      fields: [subgroups.groupId],
      references: [groups.id],
    }),
  })
);

So basically something like this:

const validResult = [ // ✅
  {
    id: '123',
    groupId: '456', // Different group ID
    slug: 'abc', // Same slug
  },
  {
    id: '123',
    groupId: '789', // Different group ID
    slug: 'abc', // Same slug
  }
]

const invalidResult = [ // ❌
  {
    id: '123',
    groupId: '456', // Same group ID
    slug: 'abc', // Same slug
  },
  {
    id: '123',
    groupId: '456', // Same group ID
    slug: 'abc', // Same slug
  }
]

const validResult = [ // ✅
  {
    id: '123',
    groupId: '456', // Different group ID
    slug: 'abc', // Same slug
  },
  {
    id: '123',
    groupId: '789', // Different group ID
    slug: 'abc', // Same slug
  }
]

const invalidResult = [ // ❌
  {
    id: '123',
    groupId: '456', // Same group ID
    slug: 'abc', // Same slug
  },
  {
    id: '123',
    groupId: '456', // Same group ID
    slug: 'abc', // Same slug
  }
]

I've been browsing through the docs I can't seem to find/understand how to do that 😅

40 replies

Gaming

Programming