Drizzle Team•2mo ago

PostgresError: foreign key constraint "product_categories_category_id_fk" cannot be implemented

Hello Drizzle users, I'm working on ecommerce store with Next.js, Drizzle ORM and Neon PostgreSQL. I was working on my schema and ran into this problem. Here's some relevant code:

export const category = pgTable('category', {
    id,
    name: text('name').notNull()
})

export const products = pgTable('product', {
    id,
    name: text('name').notNull(),
    price: integer('price').notNull(),
    description: text('description').notNull(),
    categories: uuid('categories').array().references(() => category.id, { onDelete: 'set null' }),
    // ...
})

export const category = pgTable('category', {
    id,
    name: text('name').notNull()
})

export const products = pgTable('product', {
    id,
    name: text('name').notNull(),
    price: integer('price').notNull(),
    description: text('description').notNull(),
    categories: uuid('categories').array().references(() => category.id, { onDelete: 'set null' }),
    // ...
})

When I try to migrate this schema I get following error:

PostgresError: foreign key constraint "product_categories_category_id_fk" cannot be implemented
   ...
  detail: 'Key columns "categories" and "id" are of incompatible types: uuid[] and uuid.',
  where: 'SQL statement "ALTER TABLE "product" ADD CONSTRAINT "product_categories_category_id_fk" FOREIGN KEY ("categories") REFERENCES "public"."category"("id") ON DELETE set null ON UPDATE no action"\n' +
    'PL/pgSQL function inline_code_block line 2 at SQL statement',
  file: 'tablecmds.c',
  line: '9463',
  routine: 'ATAddForeignKeyConstraint'
}

PostgresError: foreign key constraint "product_categories_category_id_fk" cannot be implemented
   ...
  detail: 'Key columns "categories" and "id" are of incompatible types: uuid[] and uuid.',
  where: 'SQL statement "ALTER TABLE "product" ADD CONSTRAINT "product_categories_category_id_fk" FOREIGN KEY ("categories") REFERENCES "public"."category"("id") ON DELETE set null ON UPDATE no action"\n' +
    'PL/pgSQL function inline_code_block line 2 at SQL statement',
  file: 'tablecmds.c',
  line: '9463',
  routine: 'ATAddForeignKeyConstraint'
}

I haven't really used that much of PostgreSQL so I don't know if this is possible, but when I'm working with MongoDB and mongoose I can just store IDs of rows from another table, and when I want to use data from that row I can just use .populate() yeah? But that seems to be an issue here.

9 Replies

TenkesOP•2mo ago

Here's repo URL: https://github.com/boristenkes/shop.co

GitHub

GitHub - boristenkes/shop.co

Contribute to boristenkes/shop.co development by creating an account on GitHub.

bhakta_jam•2mo ago

I think you need to define the shape of the id too

id: uuid("id")
      .default(sql`gen_random_uuid()`)
      .primaryKey(),

id: uuid("id")
      .default(sql`gen_random_uuid()`)
      .primaryKey(),

TenkesOP•2mo ago

I already did, just didn't include it here: const id = uuid('id').primaryKey().defaultRandom().notNull() So my schema.ts looks something like:

const id = uuid('id').primaryKey().defaultRandom().notNull()

export const category = pgTable('category', {
    id,
    name: text('name').notNull()
})

export const products = pgTable('product', {
    id,
    // ...
})

const id = uuid('id').primaryKey().defaultRandom().notNull()

export const category = pgTable('category', {
    id,
    name: text('name').notNull()
})

export const products = pgTable('product', {
    id,
    // ...
})

Winter•2mo ago

I would say the issue is the categories is being defined as an array of uuid's with a relation on it. I'm not entirely that well versed in the Drizzle schema and am having relation issues myself however I would think you would need to go through the relations API.

TenkesOP•2mo ago

Yep, that worked! Thanks a bunch, I didn't even know relations was a thing lol

Winter•2mo ago

Great to hear! I was a bit of a silly billy and had forgotten the otherside of the relation for my issue haha! In the future as well it might be a good idea to make use of https://drizzle.run 🙂

Drizzle Run

TenkesOP•2mo ago

omg that's just amazing.. THANKS! I can't add { onDelete: 'cascade' } if I'm using relations?

Winter•2mo ago

You should be able to add it within your .references That being said you should be wary of using cascade, it may seem like the right thing to do but it can be dangerous in certain scenarios. If at all possible you should wrap your delete's within a transaction

TenkesOP•2mo ago

wdym?

Gaming

Programming

PostgresError: foreign key constraint "product_categories_category_id_fk" cannot be implemented