Drizzle Team•2y ago

Schema not added to FK constraints

Hi! 👋🏽 I'm trying to use drizzle with MySQL. I have a mysqlSchema that I made and then am trying to create a FK but the generated SQL doesn't include the schema.

export const mySchema = mysqlSchema('my')

export const categories = mySchema.table('categories', {
  id: tinyint('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
})

export const items = mySchema.table('items', {
  id: int('item_id').notNull().primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
  category: tinyint('category')
    .notNull()
    .references(() => categories.id, {
      onDelete: 'cascade',
    })
})

export const mySchema = mysqlSchema('my')

export const categories = mySchema.table('categories', {
  id: tinyint('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
})

export const items = mySchema.table('items', {
  id: int('item_id').notNull().primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
  category: tinyint('category')
    .notNull()
    .references(() => categories.id, {
      onDelete: 'cascade',
    })
})

Generated:

CREATE DATABASE `my`;

CREATE TABLE `my`.`categories` (
    `id` tinyint PRIMARY KEY NOT NULL,
    `name` varchar(256) NOT NULL);

CREATE TABLE `my`.`items` (
    `item_id` int PRIMARY KEY NOT NULL,
    `name` varchar(256) NOT NULL,
    `category` tinyint NOT NULL);

ALTER TABLE `items` ADD CONSTRAINT `items_category_categories_id_fk` FOREIGN KEY (`category`) REFERENCES `categories`(`id`) ON DELETE cascade ON UPDATE no action;

CREATE DATABASE `my`;

CREATE TABLE `my`.`categories` (
    `id` tinyint PRIMARY KEY NOT NULL,
    `name` varchar(256) NOT NULL);

CREATE TABLE `my`.`items` (
    `item_id` int PRIMARY KEY NOT NULL,
    `name` varchar(256) NOT NULL,
    `category` tinyint NOT NULL);

ALTER TABLE `items` ADD CONSTRAINT `items_category_categories_id_fk` FOREIGN KEY (`category`) REFERENCES `categories`(`id`) ON DELETE cascade ON UPDATE no action;

Am I doing something wrong?

6 Replies

marcus.og•2y ago

Did you tried to delete and re-run generate one more time? IIRC I had something similar, but second run went fine.

stramelOP•2y ago

Yes, tried multiple times without success. I can try to create a minimal reproduction

Andrii Sherman•2y ago

yeah, it's a bug did fix it for Postgres didn't fix for ohcomeon

stramelOP•2y ago

whomp whomp whomp 🙃 Thank you for the confirmation! Should I log a bug for MySQL or does one exist?

Andrii Sherman•2y ago

Would be great if you could create an issue for that

stramelOP•2y ago

Filed here for reference: https://github.com/drizzle-team/drizzle-orm/issues/767

GitHub

[BUG]: Schema not added to Foreign Key constraints · Issue #767 · d...

What version of drizzle-orm are you using? 0.26.5 What version of drizzle-kit are you using? 0.18.1 Describe the Bug I'm trying to use drizzle with MySQL. I have a mysqlSchema that I made and t...

Gaming

Programming

Schema not added to FK constraints