Foreign key incorrectly formed error in MySQL in Manjaro Linux

Question

"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?

Please post the code as text instead of image, best with http://dbfiddle.uk — Lukasz Szozda, Dec 22 '19 at 16:02
I hope you are just joking now. Asking for code([Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example)) to reproduce your scenario in order to help is wasting of time? — Lukasz Szozda, Dec 22 '19 at 16:16

score 1 · Answer 1 · answered Dec 22 '19 at 17:08

1

The foreign key is across tables defined with different engines(InnoDB and MyISAM):

create table `user_role`(
`id` int auto_increment not null,
`user_id_fk` bigint(20),   -- matching data types
`role_id_fk` int(11)       -- matching data types
,primary key (`id`) 
,foreign key (`role_id_fk`) references `role`(`id`)
,foreign key (`user_id_fk`) references `user`(`id`)
);

db<>fiddle demo

Related: Why doesn't MySQL's MyISAM engine support Foreign keys?

answered Dec 22 '19 at 17:08

Lukasz Szozda

162,964
23
234
275

1

Thanks. Now, I know why it's important to upload the code. – Aye Chan Aung Thwin Dec 22 '19 at 17:42
@DraculaOppa [How does accepting an answer work?](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Lukasz Szozda Dec 22 '19 at 17:43
I didn't set the engine when I write a query. How does it happen? – Aye Chan Aung Thwin Dec 22 '19 at 17:55
1

@DraculaOppa - There is a `default_storage_engine` which is optionally set in my.cnf. Suggest that you set that to `innodb` and get in the habit of saying `ENGINE=InnoDB` on the end of `CREATE TABLE` statements. – Rick James Dec 23 '19 at 18:21
Yeah, I always make table without telling the ENGINE. Thanks for the suggestion. I'll have to put it so as not to mess up like that again. – Aye Chan Aung Thwin Dec 23 '19 at 19:07

score 1 · Accepted Answer · answered Dec 22 '19 at 21:56

1

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

answered Dec 22 '19 at 21:56

Rick James

135,179
13
127
222

Thanks, someone has already answered it for me. But I'll upvote for you. One question. I didn't tell MariaDB to use MyISAM or AnnoDB. I just created a table. How did it automatically choose MyISAM? – Aye Chan Aung Thwin Dec 23 '19 at 12:22
Old versions of MariaDB defaulted (for new tables) to MyISAM. – Rick James Dec 23 '19 at 15:26
I think the default changed with 5.5; what version are you using? Or perhaps you carried forward an old my.cnf when upgrading? – Rick James Dec 23 '19 at 18:23

Foreign key incorrectly formed error in MySQL in Manjaro Linux

2 Answers2