DT
Drizzle Team•3w ago
manikandan

Is it possible to have conditional foreign key relation?

export const booking = pgTable(
  'booking',
  {
    id: generateUUIDV4AsPrimaryKey(),
    bookingId: text().$default(() => generateBookingId()),
    userId: uuid()
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    serviceId: uuid(),
    offerId: uuid().references(() => offer.id, { onDelete: 'cascade' }),
    bookingDate: date().notNull(),
    amount: decimal({ precision: 10, scale: 2 }).notNull(),
    status: bookingStatus().default('CONFIRMED'),
    cancellationReason: text(),
    cancellationCharges: decimal({
      precision: 10,
      scale: 2,
    }),
    bookingFor: bookingFor().notNull(),
    createdAt: timestamp().defaultNow(),
    updatedAt: timestamp().defaultNow(),
  },
  (t) => [
    {
      fk_movie_id: foreignKey({
        columns: [t.serviceId],
        foreignColumns: [movie.id],
      }),
      fk_event_id: foreignKey({
        columns: [t.serviceId],
        foreignColumns: [event.id],
      }),
    },
  ],
);
export const booking = pgTable(
  'booking',
  {
    id: generateUUIDV4AsPrimaryKey(),
    bookingId: text().$default(() => generateBookingId()),
    userId: uuid()
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    serviceId: uuid(),
    offerId: uuid().references(() => offer.id, { onDelete: 'cascade' }),
    bookingDate: date().notNull(),
    amount: decimal({ precision: 10, scale: 2 }).notNull(),
    status: bookingStatus().default('CONFIRMED'),
    cancellationReason: text(),
    cancellationCharges: decimal({
      precision: 10,
      scale: 2,
    }),
    bookingFor: bookingFor().notNull(),
    createdAt: timestamp().defaultNow(),
    updatedAt: timestamp().defaultNow(),
  },
  (t) => [
    {
      fk_movie_id: foreignKey({
        columns: [t.serviceId],
        foreignColumns: [movie.id],
      }),
      fk_event_id: foreignKey({
        columns: [t.serviceId],
        foreignColumns: [event.id],
      }),
    },
  ],
);
I want to set the foreign key conditionally based on the bookingFor field value. If bookingFor = 'MOVIE' it should point to movie table, similarly if the bookingFor = 'EVENT' then it needs to point to event table.
1 Reply
TOSL
TOSL•3w ago
No you can't conditionally set FK relation What you're describing is Polymorphic Associations. In native Postgres you'd have more flexible in how you could do this but for Drizzle specifically read the following issue: https://github.com/drizzle-team/drizzle-orm/issues/1051
GitHub
[FEATURE]: Support Polymorphic Association · Issue #1051 · drizzle-...
Describe what you want I'm looking for a Typesafe ORM that support for polymorphic associations. To give a concrete example: I have a Comment model. I need this model to be associated with both...

Did you find this page helpful?