Adding foreign key that references a column which is not Primary or Unique key I got into trouble when I want to limit values of Table A's column to be taken only from a set of optional values in Table B's relevant column.

Problem is table B's relevant column isn't a foreign key.
And if I will do that I guess that the correct state where I can have the same values multiple time for different columns values will be impossible- and this is not the behavior I look for.

So what can be done?
So table2.columnB must contain a value from table1.columnA but there could be duplicate values in table1.columnA so you can't declare it unique? Apparently InnoDB does allow this, see https://stackoverflow.com/questions/2362163/mysql-foreign-key-by-non-unique-key-how-is-that-possible for discussion, but standard SQL (and therefore most databases) explicitly does not (it renders some operations like ON {DELETE|UPDATE} CASCADE potentially inconsistent) so I would avoid the option.

If there is a fixed set of valid values you could keep them in their own table with a unique/primary key defined upon the column, then both table2.columnB and  table1.columnA can be foreign keys referencing the new table.column.

A less clean option would be to write triggers to maintain the foreign key manually, as mentioned in http://cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql/ amongst other places you should find easily via your preferred search engine. You need to be careful to consider all possibilities logically and efficiently, so I would recommend the "new entity type and foreign key" method instead - that will be less bug prone, probably more efficient, and might better model your data anyway (perhaps the properties represented by those columns should be entities in their own right).