Search code examples
mysqlmariadbarchlinuxmanjaro

Foreign key incorrectly formed error in MySQL in Manjaro Linux

enter image description here

"user table" is created successfully. Every columns and types is the same as "users table." But when I create an association table named "user_role," "user table" cannot be called. I only get this error in using MySQL with MariaDB. Installing mysql in windows and using like that is fine. Without changing that table name, is there anything I can do to make it right?

Two tables are dumped because I forget where did I put my original code. But I'll be the same.

CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`course_id` bigint(20) DEFAULT NULL,
`password` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FKj8ce5cjkm11igsffixdxexrr9` (`course_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`course_id` bigint(20) DEFAULT NULL,
`password` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `course_id` (`course_id`),
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE SET NULL) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

create table `user_role`(
`id` int auto_increment not null,
`user_id_fk` bigint,
`role_id_fk` int,
primary key (`id`),
foreign key (`user_id_fk`) references `user`(`id`),
foreign key (`role_id_fk`) references `role`(`id`)
);

--> references user(id) is not working but references users(id) is working. Is there any way to make it work? Or just change the name?

Solution

  • Suggest you don't have one table name being the plural of another (user vs users); it can lead to typos.

    Don't use MyISAM for anything. (with rare exceptions)

    If that is a many-to-many mapping table follow the tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table