Theo's Typesafe Cult•15mo ago

Drizzle FKey errror

I have a posts table in postgres and it has a parentPost Foreign key to allow posts inside post (something like twitter). Heres the schema.

export const posts = pgTable(
    "post",
    {
        id: text("id").primaryKey().notNull(),
        content: varchar("content", {length: 256}).notNull(),
        createdAt: timestamp("created_at", {mode: "string"})
            .defaultNow()
            .notNull(),
        authorId: text("author_id")
            .notNull()
            .references(() => users.id, {
                onDelete: "cascade",
            }),
        parentPost: text("parent_post").default(""),
    },
    (table) => ({
        ParentPostFkey: foreignKey({
            columns: [table.parentPost],
            foreignColumns: [table.id],
        }),
    })
);

export const postRelations = relations(posts, ({one, many}) => ({
    author: one(users, {
        fields: [posts.authorId],
        references: [users.id],
    }),
    posts: many(posts),
    likes: many(likes),
    stars: many(stars),
}));

export const posts = pgTable(
    "post",
    {
        id: text("id").primaryKey().notNull(),
        content: varchar("content", {length: 256}).notNull(),
        createdAt: timestamp("created_at", {mode: "string"})
            .defaultNow()
            .notNull(),
        authorId: text("author_id")
            .notNull()
            .references(() => users.id, {
                onDelete: "cascade",
            }),
        parentPost: text("parent_post").default(""),
    },
    (table) => ({
        ParentPostFkey: foreignKey({
            columns: [table.parentPost],
            foreignColumns: [table.id],
        }),
    })
);

export const postRelations = relations(posts, ({one, many}) => ({
    author: one(users, {
        fields: [posts.authorId],
        references: [users.id],
    }),
    posts: many(posts),
    likes: many(likes),
    stars: many(stars),
}));

I'm getting this error while inserting into this table

error: insert or update on table "post" violates foreign key constraint "post_parent_post_post_id_fk"
    {
  length: 260,
  severity: 'ERROR',
  code: '23503',
  detail: 'Key (parent_post)=() is not present in table "post".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'post',
  column: undefined,
  dataType: undefined,
  constraint: 'post_parent_post_post_id_fk',
  file: 'ri_triggers.c',
  line: '2596',
  routine: 'ri_ReportViolation'
}

error: insert or update on table "post" violates foreign key constraint "post_parent_post_post_id_fk"
    {
  length: 260,
  severity: 'ERROR',
  code: '23503',
  detail: 'Key (parent_post)=() is not present in table "post".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'post',
  column: undefined,
  dataType: undefined,
  constraint: 'post_parent_post_post_id_fk',
  file: 'ri_triggers.c',
  line: '2596',
  routine: 'ri_ReportViolation'
}

Here's the api code:

await db.insert(posts).values({
            id: nanoid(10),
            content,
            authorId: session.user.id,
        });

await db.insert(posts).values({
            id: nanoid(10),
            content,
            authorId: session.user.id,
        });

3 Replies

Amir•15mo ago

I don't think Drizzle supports foreign keys. I'm not sure if I had the same exact error as you do, but in my case I had to remove all .references()

adityaOP•15mo ago

https://orm.drizzle.team/docs/indexes-constraints

Indexes & Constraints - DrizzleORM

Drizzle ORM | %s

adityaOP•15mo ago

drizzle does support foreign keys ill try removing the fkey constraint

Gaming

Programming

Drizzle FKey errror