MySQL foreign key ON DELETE SET NULL check data types error

Question

I'm working on a normalised database, to be secure 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 ;

The command:

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

The response (error):
Error creating foreign key on name (check data types)

So, how to fix this?

Answer 1

The error is self explanatory. Name in names table is of type varchar(250) whereas name_id in users table is of type int(11).

But I believe you meant to have an FK all the way around in users table referencing names table.

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

Here is SQLFiddle demo