Is there a way to say a column value should come from another table?

I have the following two schemas

export const seasonsTable = pgTable(
  'seasons',
  {
    id: serial('id').primaryKey(),
    year: integer('year').notNull(),
    start: timestamp('start').notNull(),
    end: timestamp('end').notNull(),
  },
  (table) => ({
    isUniqueSeason: unique().on(table.year),
  }),
)

export const weeksTable = pgTable(
  'weeks',
  {
    id: serial('id').primaryKey(),
    year: integer('year').notNull(),
    week: integer('week').notNull(),
    start: timestamp('start').notNull(),
    end: timestamp('end').notNull(),
  },
  (table) => ({
    isUniqueWeek: unique().on(table.year, table.week),
  }),
)

export const seasonsTable = pgTable(
  'seasons',
  {
    id: serial('id').primaryKey(),
    year: integer('year').notNull(),
    start: timestamp('start').notNull(),
    end: timestamp('end').notNull(),
  },
  (table) => ({
    isUniqueSeason: unique().on(table.year),
  }),
)

export const weeksTable = pgTable(
  'weeks',
  {
    id: serial('id').primaryKey(),
    year: integer('year').notNull(),
    week: integer('week').notNull(),
    start: timestamp('start').notNull(),
    end: timestamp('end').notNull(),
  },
  (table) => ({
    isUniqueWeek: unique().on(table.year, table.week),
  }),
)

And in the weeks table, I want to say that the year should come from the seasons table. Is that something that's possible? Is that something that is advisable? I currently grab this information from an ESPN endpoint, but figured I should probably migrate this into my own DB so I don't rely on an endpoint that could potentially get blocked.

1 Reply

rphlmr ⚡•2mo ago

Yes but not as a value. You can add a seasonsTable reference (foreign key) to weeksTable if you want to link a week row with a given season row

export const seasonsTable = pgTable(
  "seasons",
  {
    id: serial("id").primaryKey(),
    year: integer("year").notNull(),
    start: timestamp("start").notNull(),
    end: timestamp("end").notNull(),
  },
  (table) => ({
    isUniqueSeason: unique().on(table.year),
  }),
);

export const weeksTable = pgTable(
  "weeks",
  {
    id: serial("id").primaryKey(),
    seasonId: integer("season_id")
      .notNull()
      .references(() => seasonsTable.id),
    week: integer("week").notNull(),
    start: timestamp("start").notNull(),
    end: timestamp("end").notNull(),
  },
  (table) => ({
    isUniqueWeek: unique().on(table.seasonId, table.week),
  }),
);

export const seasonsTable = pgTable(
  "seasons",
  {
    id: serial("id").primaryKey(),
    year: integer("year").notNull(),
    start: timestamp("start").notNull(),
    end: timestamp("end").notNull(),
  },
  (table) => ({
    isUniqueSeason: unique().on(table.year),
  }),
);

export const weeksTable = pgTable(
  "weeks",
  {
    id: serial("id").primaryKey(),
    seasonId: integer("season_id")
      .notNull()
      .references(() => seasonsTable.id),
    week: integer("week").notNull(),
    start: timestamp("start").notNull(),
    end: timestamp("end").notNull(),
  },
  (table) => ({
    isUniqueWeek: unique().on(table.seasonId, table.week),
  }),
);

Gaming

Programming

Is there a way to say a column value should come from another table?