MariaDB: Error Code: 1005. Can't create table Foreign key constraint is incorrectly formed"

Question

I want to create UserRole table, but mariaDB complains with the following error:

Error Code: 1005. Can't create table `testdb`.`userrole` (errno: 150 "Foreign key constraint is incorrectly formed")

I don't know what I'm doing wrong, below are the tables I want to create.

create table if not exists RoleName (
id varchar(24) not null
);

create table if not exists Usr (
id integer auto_increment,
email varchar(48) not null,
usr_name varchar(48),
is_active bool default false,

constraint usr_pk primary key (id)
);

create table if not exists UserRole (
usr_id integer,
usr_role varchar(24),

constraint usr_role_fk foreign key (usr_id)
    references Usr(id),
constraint usr_role_usr_fk foreign key (usr_role)
    references RoleName(id)
);

I've searched on SO for solutions, but until now, nothing works. FK and PK are the same.

Answer 1

I got a similar a error

ERROR 1005 (HY000): Can't create table 'rhapsody.#sql-f1a_11e8cd'

when I ran

ALTER TABLE VIEW_TRACKS ADD FOREIGN KEY (TRACK_ID) REFERENCES TRACK(TRACK_ID)

because VIEW_TRACKS.TRACK_ID was defined as a varchar(20) while the referenced field TRACK.TRACKID was defined as a smallint(6).

As a workaround, I created a new key VIEW_TRACKS.TRACK_PTR of type smallint(6) and created a foreign key that referenced TRACK.TRACK_ID and it worked. It is also critical that the referenced key - TRACK.TRACKID - be either UNIque or PRImary.