MySQL foreign key ON DELETE SET NULL data type checking error

Question

I am working on a normalized database to be safe, I wanted to use foreign keys.

My database:

CREATE TABLE `names` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(250) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `name_2` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name_id` (`name_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 

Command:

 ALTER TABLE `names` ADD FOREIGN KEY ( `name` ) REFERENCES `temp`.`users` ( `name_id` ) ON DELETE SET NULL ON UPDATE CASCADE ; 

Answer (error):
Error creating foreign key by name (check data types)

So how to fix this?

Answer 1

The mistake itself. Name in the names table is of type varchar(250) , while the name_id table in users is of type int(11) .

But I believe that you wanted to have FK all the way in the users table referencing the names table.

 ALTER TABLE users ADD FOREIGN KEY (name_id) REFERENCES names (id) ON DELETE SET NULL ON UPDATE CASCADE; 

Here is the SQLFiddle demo

Answer 2

Both keys must be of the same type and length, you have varchar and int.

Here is what you want:

 ALTER TABLE `names` ADD FOREIGN KEY ( `id` ) REFERENCES `users` ( `name_id`)