manikandan Posts - Answer Overflow

manikandan

•Created by manikandan on 2/14/2025 in #help

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.

2 replies

DTDrizzle Team

•Created by manikandan on 1/8/2025 in #help

Need help with type issues on InferInsertModel and InferSelectModel