Drizzle Team•2y ago

error creating relationship

I'm having trouble creating a relationship in MySQL using drizzle-orm, I'm running npx drizzle-kit push:mysql but the I'm trying running the code inside my MySQL database but I'm getting this error

Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'anime_user_id_users_id_fk' are incompatible.

here is the schema and the generated SQL migration code.

export const users = mysqlTable(
  "users",
  {
    id: serial("id").primaryKey(),
    name: varchar("name", { length: 256 }),
    email: varchar("email", { length: 256 }).notNull(),
    image: text("image"),
  }
);

export const animes = mysqlTable(
  "anime",
  {
    id: serial("id").primaryKey(),
    userId: int("user_id").references(() => users.id),
    animeId: varchar("anime_id", { length: 256 }).notNull(),
    status: mysqlEnum("status", ["WATCHING", "HOLD"]).default("WATCHING"),
    score: int("score").default(0),
    progress: int("progress").default(0),
  },
  (animes) => ({
    animeFk: foreignKey({
      columns: [animes.userId],
      foreignColumns: [users.id],
    }),
  })
);

export const users = mysqlTable(
  "users",
  {
    id: serial("id").primaryKey(),
    name: varchar("name", { length: 256 }),
    email: varchar("email", { length: 256 }).notNull(),
    image: text("image"),
  }
);

export const animes = mysqlTable(
  "anime",
  {
    id: serial("id").primaryKey(),
    userId: int("user_id").references(() => users.id),
    animeId: varchar("anime_id", { length: 256 }).notNull(),
    status: mysqlEnum("status", ["WATCHING", "HOLD"]).default("WATCHING"),
    score: int("score").default(0),
    progress: int("progress").default(0),
  },
  (animes) => ({
    animeFk: foreignKey({
      columns: [animes.userId],
      foreignColumns: [users.id],
    }),
  })
);

CREATE TABLE `anime` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `user_id` int,
    `anime_id` varchar(256) NOT NULL,
    `status` enum('WATCHING','HOLD') DEFAULT 'WATCHING',
    `score` int DEFAULT 0,
    `progress` int DEFAULT 0
);

CREATE TABLE `users` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `name` varchar(256),
    `email` varchar(256) NOT NULL,
    `image` text
);

ALTER TABLE `anime` ADD CONSTRAINT `anime_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`);

CREATE TABLE `anime` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `user_id` int,
    `anime_id` varchar(256) NOT NULL,
    `status` enum('WATCHING','HOLD') DEFAULT 'WATCHING',
    `score` int DEFAULT 0,
    `progress` int DEFAULT 0
);

CREATE TABLE `users` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `name` varchar(256),
    `email` varchar(256) NOT NULL,
    `image` text
);

ALTER TABLE `anime` ADD CONSTRAINT `anime_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`);

2 Replies

Andrii Sherman•2y ago

Case here is that MySQL needs to have exact same types for columns, that are referencing each other id is a serial, which is an alias for bigint unsigned not null auto_increment unique So it means, that user_id should also be of type bigint unsigned Another case is that drizzle-orm doesn't have support for unsigned types(yet). So you way is to not use serial in users.id you can use those schemas

export const users = mysqlTable(
    'users',
    {
        id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
        name: varchar('name', { length: 256 }),
        email: varchar('email', { length: 256 }).notNull(),
        image: text('image'),
    },
);

export const animes = mysqlTable(
    'anime',
    {
        id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
        userId: bigint('user_id', { mode: 'number' }).references(() => users.id),
        animeId: varchar('anime_id', { length: 256 }).notNull(),
        status: mysqlEnum('status', ['WATCHING', 'HOLD']).default('WATCHING'),
        score: int('score').default(0),
        progress: int('progress').default(0),
    },
);

export const users = mysqlTable(
    'users',
    {
        id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
        name: varchar('name', { length: 256 }),
        email: varchar('email', { length: 256 }).notNull(),
        image: text('image'),
    },
);

export const animes = mysqlTable(
    'anime',
    {
        id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
        userId: bigint('user_id', { mode: 'number' }).references(() => users.id),
        animeId: varchar('anime_id', { length: 256 }).notNull(),
        status: mysqlEnum('status', ['WATCHING', 'HOLD']).default('WATCHING'),
        score: int('score').default(0),
        progress: int('progress').default(0),
    },
);

this should work fine for you

ben-sanOP•2y ago

Thank you very much for the help.

Gaming

Programming

error creating relationship