Cannot create foreign key on 2 VARCHAR(45) columns in MySQL Workbench 6.1

Question

I'm trying to create a foreign key. The columns in both tables are VARCHAR(45) and neither is a PK, however I keep getting this error message:

enter image description here

It is saying the PK of reference table is INT, which is true, but I am not referencing this column, I am referencing another column in this table that is VARCHAR(45).

I have already made to FK in this same table where both columns are VARCHAR(45), which is why I am wondering what I am doing wrong this time.

UPDATE:

Just figured it out.

The reference table column needed to be a Unique Index, then the foreign key worked.

Not a DB guy, but that sounds like a rookie mistake on my part.

• Karthick: can u share the schema or table structure or fields of table?
• ypercubeᵀᴹ: @user44730: Right, a FK should always reference either a PRIMARY or a UNIQUE key (i.e. a column or columns with a PRIMARY KEY constraint or a UNIQUE constraint).
• Colin 't Hart: Why don't you put the answer into an answer rather than an edit to the question? Answering your own questions is allowed, encouraged even.

Answer 1

From the error message, what i can deduce is that a foreign key is trying to reference a table with a primary key of int, while it also has a primary key of int set on it's table, a bit confusing.

But try removing the primary key on the referencing table like table two below, it should work fine. so you could do something like

TABLE 1 TABLE 2 Id(int)(pk) id(int) name(varchar) ------ frontname(varchar)(fk)

or

TABLE 1 TABLE 2 Id(int)(pk) id(int)(pk) name(varchar)(pk2) ------ frontname(varchar)(pk2)(fk)

Not sure of the second one though