mysql add foreign key constraint error

Question

I have two tables which I'd like to connect with foreign key constraint

For some reason, when I try to do it, it fails and and says: #1215 - Cannot add foreign key constraint

Here is my first table:

CREATE TABLE `zmaneyhayom` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `zman_id` varchar(255) NOT NULL,
 `tempHour` tinyint(1) NOT NULL,
 `tempHourType` varchar(255) NOT NULL,
 `tempHourNum` double NOT NULL,
 `tempMinutes` tinyint(1) NOT NULL,
 `tempMinutesType` varchar(255) NOT NULL,
 `tempMinutesNum` double NOT NULL,
 `regularMinutes` tinyint(1) NOT NULL,
 `regularMinutesNum` double NOT NULL,
 `equivalentMinutes` tinyint(1) NOT NULL,
 `equivalentMinutesNum` double NOT NULL,
 `degreesBelowHorizon` tinyint(1) NOT NULL,
 `degreesBelowHorizonNum` double NOT NULL,
 `beforeAfter` varchar(6) NOT NULL,
 `riseSet` varchar(4) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `zman_id_2` (`zman_id`),
 KEY `zman_id` (`zman_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And this table holds ID and name, which eventually the ID in this table is the name for the previous table (zman_id column):

CREATE TABLE `zmaneyhayomlabels` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This is the code I'm attempting in order to create the constraint:

ALTER TABLE  `zmaneyhayom` ADD FOREIGN KEY (  `zman_id` ) REFERENCES  `luah_v2`.`zmaneyhayomlabels` (

`id`
) ON DELETE NO ACTION ON UPDATE NO ACTION ;

I have no idea why it's failing :/

What I want is that whenever I go on phpmyadmin and go to the first table, instead of typing some id in zman_id I will have a select box which I can select a name (which is stored in the second table) but the value it will store will be the ID.

Answer 1

they are not same type id is INT and zman_id is varchar.

you can change this

 `zman_id` varchar(255) NOT NULL,

to

 `zman_id` int(11) NOT NULL,