CODEWITHSUNDEEP
mysql
Sa'Kagé
Sa'Kagé

Reputation: 51

MySQL foreign key cannot be created (index problems?)

I have a little problem making some foreign keys...
When I try to define the foreign key in MySQL Workbench, I get the following message:

Selected column 'playerName' must be indexed and be of a compatible type for a Foreign Key to be created.

There my problem starts: I'm pretty sure, that the column (towns.playerName) is indexed and it's definitively of the same type ( VARCHAR(255) )...
Indexes of 'towns'
I want to add a Foreign Key from players.name (primary key, not null, unique) to towns.playersName(not null).
So what can I do to get the foreign key created?
It seems i am doing something wrong...

PS: I'm sorry, if there is already a question for this...

EDIT: I just tried again (exactly as I did before several times) and now it works... really strange
Perhaps a bug in MySQL Workbench??

Upvotes: 5

Views: 21971

Answers (3)

ASK
ASK

Reputation: 1274

I got the same problem a number of times, but finally found a interesting and useful concept which I missed while learning mysql. If a column is not a 'key' in the table 1, you cannot add it as foreign key unless it should have an index. So I make the column a indexed column.

e.g.

CREATE INDEX any_name ON table1 (column1);

finally, I was able to solve my problem.

Upvotes: 6

Saurabh Rana
Saurabh Rana

Reputation: 167

I was getting similar error message. I checked the type of the column in both the tables. It was int in one table whereas varchar in the other table. It should be the same type in both. On using the same type in both the tables, it worked fine.

Upvotes: 0

Ted Johansson
Ted Johansson

Reputation: 1

I believe that the key you are trying to access through the foreign key needs to be a primary key in the other table.

Upvotes: 0

Related Questions