ben-san Posts - Answer Overflow

ben-san

Explore posts from servers

•Created by ben-san on 5/5/2023 in #help

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`);

4 replies

Gaming

Programming