Drizzle Team•5mo ago

PostgresError: column <column_name> referenced in foreign key constraint does not exist

I have this simple schema in which I am trying to create a one-to-many relationship between option and optionValue tables. However when running migration script I get the following error. PostgresError: column "option_id" referenced in foreign key constraint does not exist

import { pgPrefixIdKey } from "@/utils/drizzle"
import { relations } from "drizzle-orm"
import { pgTable, text, varchar } from "drizzle-orm/pg-core"

export const option = pgTable("option", {
  id: pgPrefixIdKey({ prefix: "option" }).primaryKey(),
  name: varchar("name", { length: 64 }).notNull(),
})

export const optionRelations = relations(option, ({ many }) => ({
  values: many(optionValue),
}))

export const optionValue = pgTable("option_value", {
  id: pgPrefixIdKey({ prefix: "option_value" }).primaryKey(),
  value: varchar("value", { length: 64 }).notNull(),
  optionId: text("option_id")
    .notNull()
    .references(() => option.id),
})

export const optionValueRelations = relations(optionValue, ({ one }) => ({
  option: one(option, {
    fields: [optionValue.optionId],
    references: [option.id],
  }),
}))

import { pgPrefixIdKey } from "@/utils/drizzle"
import { relations } from "drizzle-orm"
import { pgTable, text, varchar } from "drizzle-orm/pg-core"

export const option = pgTable("option", {
  id: pgPrefixIdKey({ prefix: "option" }).primaryKey(),
  name: varchar("name", { length: 64 }).notNull(),
})

export const optionRelations = relations(option, ({ many }) => ({
  values: many(optionValue),
}))

export const optionValue = pgTable("option_value", {
  id: pgPrefixIdKey({ prefix: "option_value" }).primaryKey(),
  value: varchar("value", { length: 64 }).notNull(),
  optionId: text("option_id")
    .notNull()
    .references(() => option.id),
})

export const optionValueRelations = relations(optionValue, ({ one }) => ({
  option: one(option, {
    fields: [optionValue.optionId],
    references: [option.id],
  }),
}))

I am new to postgres and relational databases in general and I don't get what is causing this. What I think I am doing is adding a values column to my option table, and then assigning optionId column of my optionValue table to referenced value of option.id column. I am pretty sure this is how it's also realized in the docs, but I might've overlooked something...

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

PostgresError: column <column_name> referenced in foreign key constraint does not exist