Tenkes Posts - Answer Overflow

Tenkes

Explore posts from servers

KPCKevin Powell - Community DTDrizzle Team

•Created by Tenkes on 9/22/2024 in #help

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.

14 replies

Gaming

Programming