给现有表添加外键

Question

我想给名为“katalog”的表添加一个外键。

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

当我尝试执行此操作时，出现以下错误消息：

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

INNODB状态报错:

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

当我使用这个查询时，它可以工作，但"on delete"操作错误：

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

两个表都是InnoDB引擎，两个字段都是“INT（11）not null”。我使用的是MySQL 5.1.61版本。我正在尝试在MacBook Pro上使用MySQL Workbench（最新版本）执行此ALTER查询。

表创建语句：

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

- frgtv10

---

由于您没有发布“SHOW CREATE TABLE”的输出，我只能问一下 - 列名真的是大写的ID吗？ - N.B.

现在更容易发现了 - katalog 具有 int(11) unsigned。sprache 没有 usigned 部分，因此两列不同。 - N.B.

你的意思是，两个主键字段必须是相同的数据类型吗？ - frgtv10

您设计的问题在于：首先，您引用了两个auto_increment列，这是不好的。此外，MySQL手册指出：“外键和参考键中的对应列必须在InnoDB中具有类似的内部数据类型，以便它们可以进行比较而无需类型转换。整数类型的大小和符号必须相同。字符串类型的长度不需要相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。”因此，是的，需要相似的数据类型和相同的符号。 - N.B.

我没有引用两个自增字段。 katalog.Sprache（非自增）-> sprache.ID（自增） - frgtv10

对N.B.的补充：在我的情况下，我有一个脚本，在约束定义中表名的第一个字母没有大写。 - Aaron Newton

Answer 1

要向现有表(users)添加外键(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);

- iltaf khalid

---

帮助我理解和记忆的原因是因为您不能将外键添加到非无符号字段，对吗？ - Z2VvZ3Vp

@PixMach，答案是否定的。您可以将有符号整数作为外键。正如N.B.在问题中指出的那样，字段的类型和符号必须匹配。因此，如果查找表中的主键为UNSIGNED，则外键字段也必须为UNSIGNED。如果主键字段为SIGNED，则外键字段也必须为有符号的。可以这样想：在一个表中定义的列在SHOW CREATE TABLE中显示的定义必须与另一个表中的定义相同。 - Ben Keene

请注意，这也可以用单个查询完成（如果发生故障等情况可能更好）。 - Stijn de Witt

在运行ADD CONSTRAINT命令之前，我必须运行“SET FOREIGN_KEY_CHECKS=0;”，否则SQL会报错“无法添加或更新子行：外键约束失败”。 - Erin Geyer

如果出现“外键约束失败”的错误，不要只运行“SET FOREIGN_KEY_CHECKS=0;”，因为这明显表示你有坏数据需要修复，否则将会在以后遇到更大的问题。请注意不要改变原意。 - MazeChaZer

Answer 2

只需使用这个查询，按照我的情况尝试了一下，它可以很好地工作

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

- SagarPPanchal

---

您需要添加一个警告/免责声明，说明这将会为您创建一个约束名称。在处理数据库迁移并且您想要修改约束时，这可能会产生严重的后果。命名是一个很好的实践习惯。 - TheRealChx101

如有任何错误，请阅读此链接：https://dev59.com/Kmoy5IYBdhLWcg3wnPWJ - C.F.G

Answer 3

简单步骤...

ALTER TABLE t_name1 ADD FOREIGN KEY (column_name) REFERENCES t_name2(column_name)

- Amjath Khan

---

如有任何错误，请阅读此链接：https://dev59.com/Kmoy5IYBdhLWcg3wnPWJ - C.F.G

Answer 4

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

但是您的表格包含：

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

由于Sprache列被定义为NOT NULL，因此无法将其设置为NULL。

- Bill

Answer 5

请查看此链接，它帮助我解决了errno 150的问题：http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/

脑海中有两件事情需要考虑。

• 您的外键索引在整个数据库中是否具有唯一名称（列表中的＃3）？
• 您是否试图将表格PK设置为NULL以进行更新（列表中的＃5）？

我想问题可能出现在更新时设置为NULL（如果我的大脑今天没有反向的话...）。

编辑：我错过了您原始帖子中的评论。无符号/有符号int列可能已解决您的问题。希望我的链接能帮助未来的某个人。

- ZZ-bb

Answer 6

如何解决在mysql中出现的Error Code: 1005. Can't create table 'mytable.#sql-7fb1_7d3a' (errno: 150)错误。

1. alter your table and add an index to it..

   ALTER TABLE users ADD INDEX index_name (index_column)
   

2. Now add the constraint

   ALTER TABLE foreign_key_table
   ADD CONSTRAINT foreign_key_name FOREIGN KEY (foreign_key_column)
   REFERENCES primary_key_table (primary_key_column) ON DELETE NO ACTION
   ON UPDATE CASCADE;
   

请注意，如果您不添加索引，则无法正常工作。

在与此问题搏斗了约6个小时后，我找到了解决方案。希望这能拯救某个灵魂。

- Kwed

Answer 7

MySQL将执行此查询：

ALTER TABLE `db`.`table1`
ADD COLUMN `col_table2_fk` INT UNSIGNED NULL,
ADD INDEX `col_table2_fk_idx` (`col_table2_fk` ASC),
ADD CONSTRAINT `col_table2_fk1`
FOREIGN KEY (`col_table2_fk`)
REFERENCES `db`.`table2` (`table2_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

Cheers!

- akelec

Answer 8

尝试使用单个查询的全部内容

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

- manoj

Answer 9

当您使用ALTER TABLE向表中添加外键约束时，请记得首先创建所需的索引。

1. 创建索引
2. 修改表

- Maksym Polshcha

---

我为两者创建了索引。当我尝试运行时，我得到了与之前相同的错误信息。当我否认“on delete on update”部分时，它可以工作，但却采用了错误的“on delete”操作;) - frgtv10

@frgtv10，最有可能是表格数据与“on delete”冲突。 - Maksym Polshcha

Answer 10

步骤1：运行此脚本

SET FOREIGN_KEY_CHECKS=0;

步骤2：添加列

ALTER TABLE mileage_unit ADD COLUMN COMPANY_ID BIGINT(20) NOT NULL

步骤三：向新增的列添加外键

ALTER TABLE mileage_unit
ADD FOREIGN KEY (COMPANY_ID) REFERENCES company_mst(COMPANY_ID);

步骤 4：运行此脚本

SET FOREIGN_KEY_CHECKS=1;

- rushikesh satpute