error 1215 while trying to create foreign key

Asked Aug 18 '20 at 20:46

Active Aug 18 '20 at 20:55

Viewed 34 times

I'm trying to create roles management system and I get error while trying to create mysql table with foreign keys. I checked w3school documents and everything seems to be same.

My error:

#1215 - Cannot add foreign key constraint

MySQL:

CREATE TABLE user_role (
  user_id INTEGER UNSIGNED NOT NULL,
  role_id INTEGER UNSIGNED NOT NULL,

  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (role_id) REFERENCES roles(role_id)
);

I run this command:

SHOW ENGINE INNODB STATUS;

And got this:

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.

edited Aug 18 '20 at 20:55

asked Aug 18 '20 at 20:46

jmb86982

2

You need to show us the `create table` statements for tables `users` and `roles`. – GMB Aug 18 '20 at 20:48
Have a look at this answer https://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint – keidakida Aug 18 '20 at 20:48
@keidakida edited post – jmb86982 Aug 18 '20 at 20:56
Aren't user_id and role_id composite primary keys in the user_role table? You haven't marked them as such. – Honeyboy Wilson Aug 18 '20 at 21:03
Could you show the users and roles create table query? Could you also check the collation for your table and database are the same? – keidakida Aug 18 '20 at 21:11

error 1215 while trying to create foreign key

0 Answers0