Can you add a unique to an existing table? supabase + drizzle

I am using supabase and drizzle in my Next.js application and unfortunately noticed a year later I forgot to add a

unique().on()

unique().on()

for a table that holds voter ballots.

I thought everything was fine and dandy and no duplicates were being inserted into the DB until this week when I noticed some.

The schema for this table looks like

export const voterBallots = pgTable(
  'voter_ballot',
  {
    id: serial('id').primaryKey(),
    userId: varchar('userId', { length: 256 }).notNull(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year')
      .default(sql`EXTRACT(year FROM CURRENT_DATE)`)
      .notNull(),
    createdAt: timestamp('created_at')
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    teamId: varchar('team_id', { length: 256 }).notNull(),
    rank: integer('rank').notNull(),
    points: integer('points').notNull(),
    sportId: varchar('sport_id', { length: 256 }).references(() => sportsTable.id),
  }
)

export const voterBallots = pgTable(
  'voter_ballot',
  {
    id: serial('id').primaryKey(),
    userId: varchar('userId', { length: 256 }).notNull(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year')
      .default(sql`EXTRACT(year FROM CURRENT_DATE)`)
      .notNull(),
    createdAt: timestamp('created_at')
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    teamId: varchar('team_id', { length: 256 }).notNull(),
    rank: integer('rank').notNull(),
    points: integer('points').notNull(),
    sportId: varchar('sport_id', { length: 256 }).references(() => sportsTable.id),
  }
)

And I am thinking of adding

(table) => [unique().on(table.userId, table.division, table.week, table.year, table.sportId)],

(table) => [unique().on(table.userId, table.division, table.week, table.year, table.sportId)],

to it and then generating and then migrating. I guess my question is, will this break anything? I believe I removed all the duplicates and want to make sure I get this in there before more votes get added.

Can you add a unique to an existing table? supabase + drizzle

I am using supabase and drizzle in my Next.js application and unfortunately noticed a year later I forgot to add a

unique().on()

unique().on()

for a table that holds voter ballots.

I thought everything was fine and dandy and no duplicates were being inserted into the DB until this week when I noticed some.

The schema for this table looks like

export const voterBallots = pgTable(
  'voter_ballot',
  {
    id: serial('id').primaryKey(),
    userId: varchar('userId', { length: 256 }).notNull(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year')
      .default(sql`EXTRACT(year FROM CURRENT_DATE)`)
      .notNull(),
    createdAt: timestamp('created_at')
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    teamId: varchar('team_id', { length: 256 }).notNull(),
    rank: integer('rank').notNull(),
    points: integer('points').notNull(),
    sportId: varchar('sport_id', { length: 256 }).references(() => sportsTable.id),
  }
)

export const voterBallots = pgTable(
  'voter_ballot',
  {
    id: serial('id').primaryKey(),
    userId: varchar('userId', { length: 256 }).notNull(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year')
      .default(sql`EXTRACT(year FROM CURRENT_DATE)`)
      .notNull(),
    createdAt: timestamp('created_at')
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    teamId: varchar('team_id', { length: 256 }).notNull(),
    rank: integer('rank').notNull(),
    points: integer('points').notNull(),
    sportId: varchar('sport_id', { length: 256 }).references(() => sportsTable.id),
  }
)

And I am thinking of adding

(table) => [unique().on(table.userId, table.division, table.week, table.year, table.sportId)],

(table) => [unique().on(table.userId, table.division, table.week, table.year, table.sportId)],

Can you add a unique to an existing table? supabase + drizzle

Similar Threads

Can you add a unique to an existing table? supabase + drizzle

Similar Threads

Similar Threads

Similar Threads