5

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

Sa'Kagé
  • 51
  • 1
  • 1
  • 4
  • `towns.playersName` must have an index on it too. newer mysql versions would create the necessary index automatically, but on older versions, you have to index the fields-used-in-foreign-key yourself. – Marc B Feb 23 '15 at 18:57
  • `towns.playerName` **is** indexed... perhaps not totally clear; edited question – Sa'Kagé Feb 23 '15 at 19:17
  • Try to create the foreign key the opposite way. The constraint/foreign key must be on the table not having the unique index. Check this post out for an example. http://stackoverflow.com/questions/18383182/mysql-table-with-a-varchar-column-as-foreign-key Also, it could be useful if you post more sql. – jornare Feb 23 '15 at 19:27

3 Answers3

6

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.

ASK
  • 1,136
  • 1
  • 10
  • 14
0

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

Ted Johansson
  • 1
  • Not required. Even Non-Primary keys can be referenced as Foreign Key but, they have to be indexed. – learner Jan 14 '20 at 12:17
0

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.

AS Mackay
  • 2,831
  • 9
  • 19
  • 25
Saurabh Rana
  • 168
  • 3
  • 22