Keep getting relation constraint error when migrating

I have the following table where I set both the userId and eventId unique.

export const userToEvent = pgTable(
  'user_to_event',
  {
    id: varchar('id')
      .notNull()
      .primaryKey()
      .$defaultFn(() => generateId('user_to_event')),
    userId: varchar('user_id')
      .notNull()
      .references(() => user.id),
    eventId: varchar('event_id')
      .notNull()
      .references(() => event.id),
    paymentId: varchar('payment_id')
      .notNull()
      .references(() => payment.id),
    registeredAt: timestamp('registered_at', { mode: 'date' }),
    eventConfirmation: varchar('event_confirmation'),
  },
  (table) => {
    return {
      eventConfirmationIdx: index('event_confirmation_idx').on(
        table.eventConfirmation,
      ),
      userIdEventIdUniq: unique().on(table.userId, table.eventId),
    }
  },
)

export const userToEvent = pgTable(
  'user_to_event',
  {
    id: varchar('id')
      .notNull()
      .primaryKey()
      .$defaultFn(() => generateId('user_to_event')),
    userId: varchar('user_id')
      .notNull()
      .references(() => user.id),
    eventId: varchar('event_id')
      .notNull()
      .references(() => event.id),
    paymentId: varchar('payment_id')
      .notNull()
      .references(() => payment.id),
    registeredAt: timestamp('registered_at', { mode: 'date' }),
    eventConfirmation: varchar('event_confirmation'),
  },
  (table) => {
    return {
      eventConfirmationIdx: index('event_confirmation_idx').on(
        table.eventConfirmation,
      ),
      userIdEventIdUniq: unique().on(table.userId, table.eventId),
    }
  },
)

When I run drizzle-kit generate -> drizzle-kit migrate, it fails saying applying migrations...error: constraint "user_to_event_user_id_unique" of relation "user_to_event" does not exist. Here's how my db/schema directory looks like:

// src/lib
--db
  -- schema
    -- some-schemas.schema.ts
    -- ...more schemas

    -- index.ts  <- where I export all schema files as "export * from './some-schema.schema'"

// src/lib
--db
  -- schema
    -- some-schemas.schema.ts
    -- ...more schemas

    -- index.ts  <- where I export all schema files as "export * from './some-schema.schema'"

And here's my drizzle config:

import { defineConfig } from 'drizzle-kit'
import { env } from '@/lib/env'

export default defineConfig({
  schema: './src/lib/db/schema/index.ts',
  dialect: 'postgresql',
  migrations: {
    table: 'migrations',
    schema: 'public',
  },
  dbCredentials: {
    url: env.DATABASE_URL,
  },
  out: './drizzle',
})

import { defineConfig } from 'drizzle-kit'
import { env } from '@/lib/env'

export default defineConfig({
  schema: './src/lib/db/schema/index.ts',
  dialect: 'postgresql',
  migrations: {
    table: 'migrations',
    schema: 'public',
  },
  dbCredentials: {
    url: env.DATABASE_URL,
  },
  out: './drizzle',
})

When I dropped all of my migrations and ran generate -> migrate, it worked. Am I doing something wrong? Thanks!!

2 Replies

web_dev_dkOP•6mo ago

btw, I'm using

- "drizzle-orm": "^0.31.2",
- "drizzle-kit": "^0.22.7",

- "drizzle-orm": "^0.31.2",
- "drizzle-kit": "^0.22.7",

zendev•5mo ago

I am running into almost the identical issue Did you manage to resolve this or figure out what was going wrong? Without having to drop all the migrations preferably lol

Gaming

Programming

Keep getting relation constraint error when migrating