column "foo" referenced in foreign key constraint does not exist

i got an error when i migrating, the error is this:

PostgresError: column "person_id" referenced in foreign key constraint does not exist

PostgresError: column "person_id" referenced in foreign key constraint does not exist

this is the query that throw error:

DO $$ BEGIN
 ALTER TABLE "story" ADD CONSTRAINT "story_person_id_person_id_fk" FOREIGN KEY ("person_id") REFERENCES "public"."person"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "story" ADD CONSTRAINT "story_person_id_person_id_fk" FOREIGN KEY ("person_id") REFERENCES "public"."person"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

you can see the schema in the reply.

1 Reply

pppppOP•4mo ago

table person:

import { relations } from "drizzle-orm";
import { date, pgTable, serial, timestamp, varchar } from "drizzle-orm/pg-core";
import { story } from "./story";

export const person = pgTable("person", {
    id: serial("id").primaryKey(),
    email: varchar("email", { length: 255 }).notNull(),
    password: varchar("password", { length: 255 }).notNull(),
    fullname: varchar("fullname", { length: 255 }).notNull(),
    birthDate: date("birth_date"),
    createdAt: timestamp("created_at", { mode: "string" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "string" }).notNull().defaultNow(),
});

export const personRelations = relations(person, ({ many }) => ({
    story: many(story, { relationName: "person-story" }),
}));

export type SelectPerson = typeof person.$inferSelect;
export type InsertPerson = typeof person.$inferInsert;

import { relations } from "drizzle-orm";
import { date, pgTable, serial, timestamp, varchar } from "drizzle-orm/pg-core";
import { story } from "./story";

export const person = pgTable("person", {
    id: serial("id").primaryKey(),
    email: varchar("email", { length: 255 }).notNull(),
    password: varchar("password", { length: 255 }).notNull(),
    fullname: varchar("fullname", { length: 255 }).notNull(),
    birthDate: date("birth_date"),
    createdAt: timestamp("created_at", { mode: "string" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "string" }).notNull().defaultNow(),
});

export const personRelations = relations(person, ({ many }) => ({
    story: many(story, { relationName: "person-story" }),
}));

export type SelectPerson = typeof person.$inferSelect;
export type InsertPerson = typeof person.$inferInsert;

table story:

import { relations } from "drizzle-orm";
import {
    type PgColumn,
    date,
    index,
    integer,
    pgTable,
    serial,
    timestamp,
    unique,
    varchar,
} from "drizzle-orm/pg-core";
import { person } from "./person";

export const story = pgTable(
    "story",
    {
        id: serial("id").primaryKey(),
        personId: integer("person_id")
            .notNull()
            .references((): PgColumn => person.id),
        date: date("date").notNull(),
        content: varchar("content", { length: 255 }).notNull(),
        createdAt: timestamp("created_at", { mode: "string" })
            .notNull()
            .defaultNow(),
        updatedAt: timestamp("updated_at", { mode: "string" })
            .notNull()
            .defaultNow(),
    },
    (t) => ({
        PersonStoryDateUnq: unique("person_story_date").on(t.date, t.personId),
        personIdx: index("person_idx").using("hash", t.personId.asc()),
    }),
);

export const storyRelations = relations(story, ({ one }) => ({
    person: one(person, {
        fields: [story.personId],
        references: [person.id],
        relationName: "person-story",
    }),
}));

export type SelectStory = typeof story.$inferSelect;
export type InsertStory = typeof story.$inferInsert;

import { relations } from "drizzle-orm";
import {
    type PgColumn,
    date,
    index,
    integer,
    pgTable,
    serial,
    timestamp,
    unique,
    varchar,
} from "drizzle-orm/pg-core";
import { person } from "./person";

export const story = pgTable(
    "story",
    {
        id: serial("id").primaryKey(),
        personId: integer("person_id")
            .notNull()
            .references((): PgColumn => person.id),
        date: date("date").notNull(),
        content: varchar("content", { length: 255 }).notNull(),
        createdAt: timestamp("created_at", { mode: "string" })
            .notNull()
            .defaultNow(),
        updatedAt: timestamp("updated_at", { mode: "string" })
            .notNull()
            .defaultNow(),
    },
    (t) => ({
        PersonStoryDateUnq: unique("person_story_date").on(t.date, t.personId),
        personIdx: index("person_idx").using("hash", t.personId.asc()),
    }),
);

export const storyRelations = relations(story, ({ one }) => ({
    person: one(person, {
        fields: [story.personId],
        references: [person.id],
        relationName: "person-story",
    }),
}));

export type SelectStory = typeof story.$inferSelect;
export type InsertStory = typeof story.$inferInsert;

Gaming

Programming

column "foo" referenced in foreign key constraint does not exist