2

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.

  • can u share the schema or table structure or fields of table? – Karthick Jul 31 '14 at 10:31
  • @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). – ypercubeᵀᴹ Jul 31 '14 at 10:51
  • 1
    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. – Colin 't Hart Jul 31 '14 at 11:58
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.