ben-san
ben-san
Explore posts from servers
DTDrizzle Team
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