MySQL外键ON DELETE SET NULL检查数据类型错误

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?

那么,如何解决这个问题呢?

2 个解决方案

#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).

错误是自我解释的。名称表中的名称是varchar(250)类型,而users表中的name_id是int(11)类型。

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

但我相信你的意思是在用户表引用名称表中一直有一个FK。

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

Here is SQLFiddle demo

这是SQLFiddle演示

#2

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

两个键必须是相同的类型和长度,你有varchar和int。

Here is what you want:

这是你想要的:

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

#1