Insert trigger for foreign key string to int

EDIT Just to add a bit of clarity to what I'm trying to achieve with this design. Data will be inserted into tbl1 which will be a user entry. My intention is to store the different possible values for foo in tbl2. Such that there is a unique ID attached to each possible foo value.

END EDIT

I have two tables with syntax:

CREATE TABLE `tbl1` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `foo` int(10) NOT NULL default '0',
    PRIMARY KEY (`id`),
    KEY(`foo`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `tbl2` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `value` varchar(100) NOT NULL default '',
    PRIMARY KEY(`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


DELIMITER //
DROP TRIGGER `trigger1`//
CREATE TRIGGER `trigger1` BEFORE INSERT ON `tbl1` 
FOR EACH ROW BEGIN
    DECLARE x INT(10);
    IF (SELECT COUNT(*) FROM `tbl2` WHERE `value`= NEW.foo) = 0 THEN INSERT INTO `tbl2` (`value`) VALUES (NEW.foo); SET x=last_insert_id();\
ELSE SET x=(SEL开发者_运维知识库ECT `id` FROM `tbl2` WHERE `value`= NEW.foo);
END IF;

SET NEW.foo=x;
END//
DELIMITER ;

The problem is that MySQL is rejecting a varchar value for foo and thus an insert as follows: INSERT INTO tbl1 (foo) VALUES ('bar'); Will insert a record into tbl2 with value 0.

Are there any other approaches to this that can achieve the correct result (without changing foo to a varchar)?

It won't work because you're trying to insert 'bar' a string into the table field foo for tbl1 wich is of type int, and according to this:

If you try to store a string that doesn't start with a number into a numeric column, MySQL Server stores 0.

So before the trigger gets executed the value 'bar' is converted to 0.