#1072 – Key column ‘role_id’ doesn’t exist in table

I am trying to add foreign key on my other table but this gave me error #1072 - Key column 'role_id' doesn't exist in table

I have created a table named role

then I created like this

create table role (
  role_id varchar(15)
  primary key (role_id)
)

then when I try to alter table on my user table

alter table user
add foreign key (role_id)
references role(role_id)

and I got an error like this

#1072 - Key column 'role_id' doesn't exist in table

Answer

You have to have the column you reference in add foreign key (role_id) inside your user table. Otherwise you get that error.

You would have to have inside your user table something like:

create table user(
  ...
  role_id varchar(15)
  ...
)

Or if you don’t have it, you have to do:

ALTER TABLE user ADD COLUMN role_id VARCHAR(15)

Before you set it as a foreign key.