将外键添加到现有表

本文翻译自：Add Foreign Key to existing table

I want to add a Foreign Key to a table called "katalog". 我想将外键添加到名为“ katalog”的表中。

ALTER TABLE katalog 
ADD CONSTRAINT `fk_katalog_sprache` 
FOREIGN KEY (`Sprache`)
REFERENCES `Sprache` (`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL;

When I try to do this, I get this error message: 当我尝试执行此操作时，出现以下错误消息：

 Error Code: 1005. Can't create table 'mytable.#sql-7fb1_7d3a' (errno: 150) 

Error in INNODB Status: INNODB状态错误：

120405 14:02:57 Error in foreign key constraint of table mytable.#sql-7fb1_7d3a: 120405 14:02:57表mytable的外键约束错误。＃sql-7fb1_7d3a：

FOREIGN KEY (`Sprache`)
REFERENCES `Sprache` (`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL:
Cannot resolve table name close to:
(`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL

When i use this query it works, but with wrong "on delete" action: 当我使用此查询时，它可以工作，但是执行“删除时”操作错误：

ALTER TABLE `katalog` 
ADD FOREIGN KEY (`Sprache` ) REFERENCES `sprache` (`ID` )

Both tables are InnoDB and both fields are "INT(11) not null". 两个表都是InnoDB，两个字段都是“ INT（11）not null”。 I'm using MySQL 5.1.61. 我正在使用MySQL 5.1.61。 Trying to fire this ALTER Query with MySQL Workbench (newest) on a MacBook Pro. 尝试在MacBook Pro上使用MySQL Workbench（最新）触发此ALTER Query。

Table Create Statements: 表创建语句：

CREATE TABLE `katalog` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`AnzahlSeiten` int(4) unsigned NOT NULL,
`Sprache` int(11) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `katalogname_uq` (`Name`)
 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC$$

CREATE TABLE `sprache` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
 `Bezeichnung` varchar(45) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Bezeichnung_UNIQUE` (`Bezeichnung`),
KEY `ix_sprache_id` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

---

#1楼

参考：https://stackoom.com/question/G4No/将外键添加到现有表

---

#2楼

When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first. 使用ALTER TABLE将外键约束添加到表时，请记住首先创建所需的索引。

1. Create index 创建索引
2. Alter table 修改表

---

#3楼

check this link. 检查此链接。 It has helped me with errno 150: http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/ 它帮助我解决了errno 150： http : //verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/

On the top of my head two things come to mind. 在我的头上，有两件事想起。

• Is your foreign key index a unique name in the whole database (#3 in the list)? 您的外键索引在整个数据库中是否是唯一名称（列表中的＃3）？
• Are you trying to set the table PK to NULL on update (#5 in the list)? 您是否要在更新时将表PK设置为NULL（列表中的＃5）？

I'm guessing the problem is with the set NULL on update (if my brains aren't on backwards today as they so often are...). 我猜测问题出在更新时设置了NULL（如果我的大脑今天不像往常那样倒退……）。

Edit: I missed the comments on your original post. 编辑：我错过了对您的原始帖子的评论。 Unsigned/not unsigned int columns maybe resolved your case. Unsigned / not unsigned int列可能会解决您的情况。 Hope my link helps someone in the future thought. 希望我的链接对以后的想法有所帮助。

---

#4楼

To add a foreign key (grade_id) to an existing table (users), follow the following steps: 要将外键（grade_id）添加到现有表（用户），请按照下列步骤操作：

ALTER TABLE users ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE users ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) REFERENCES grades(id);

---

#5楼

FOREIGN KEY (`Sprache`)
REFERENCES `Sprache` (`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL;

But your table has: 但是您的表有：

CREATE TABLE `katalog` (
`Sprache` int(11) NOT NULL,

It cant set the column Sprache to NULL because it is defined as NOT NULL. 它不能将列Sprache设置为NULL，因为它被定义为NOT NULL。

---

#6楼

只需使用此查询，我就根据自己的情况进行了尝试，并且效果很好

ALTER TABLE katalog ADD FOREIGN KEY (`Sprache`) REFERENCES Sprache(`ID`);