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
Andrii Sherman15mo 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-san
ben-san15mo ago
Thank you very much for the help.