MySQL Bugs: #60219: defining a foreign key does not allow to check a none PK column in the src tb

Bug #60219	defining a foreign key does not allow to check a none PK column in the src tb
Submitted:	23 Feb 2011 9:36	Modified:	23 Feb 2011 18:30
Reporter:	Eitan Weisbeker	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.31	OS:	Windows (revision 7115)
Assigned to:		CPU Architecture:	Any
Tags:	workbench;modeler;foreign key;fk;

Description:
trying to define a foriegn from a source table to a destination table is problematic - it does not allow to choose any field from the source table other than the PK.
automatically an index is created in the source table for the choosen column

How to repeat:
create 2 tables, each with 1 column as the PK, and 2 other columns of other types.
try to ref column2 (not the PK of the source table, anyother column) from t1 to column1 of t2 which is the PK of that table.

Suggested fix:
allow to choose (check box) any of the fields or combination of fields in the source table.

Why do you think this is a bug? It is normal that foreign key may reference only UNIQUE key (and, thus, a primary key). 

Yes, it is possible for InnoDB table to have foreign key referencing some index columns, not primary key (read http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html), but this is still a kind of exception:

"Deviation from SQL standards: A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL. It is an InnoDB extension to standard SQL."

Referencing just arbitrary column should not be supported, IMHO.

i mentioned that the problem is in selecting the columns of the source table. not the destination table were you would expect the column to be the PK.
i want to reference source_table.regular_column to destination_table_pk_column.
i can't select , using the workbench UI, any column i want, rather i am forced to choose the PK of the source table.

see the section i wrote in "how to reproduce the problem".

Then this is, probably, because their data types are different from data type of any PK in any table in the model. From the same manual page:

"Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same. "

my apologies, they indeed had data type mismatch.
they were both BIGINT but the destination PK was marked with UnSigned and the source column was missing that indication.
you can close the ticket\bug

thx for the reponsiveness