MySQL 将外键添加到现有表

Add Foreign Key to existing table

提问by frgtv10

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 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". I'm using MySQL 5.1.61. Trying to fire this ALTER Query with MySQL Workbench (newest) on a MacBook Pro.

两个表都是 InnoDB 并且两个字段都是“INT(11) not null”。我正在使用 MySQL 5.1.61。尝试在 MacBook Pro 上使用 MySQL Workbench（最新）触发此 ALTER 查询。

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

回答by iltaf khalid

To add a foreign key (grade_id) to an existing table (users), follow the following steps:

要将外键 (grade_id) 添加到现有表 (users)，请按照以下步骤操作：

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);

回答by SagarPPanchal

Simply use this query, I have tried it as per my scenario and it works well

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

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

回答by Amjath Khan

Simple Steps...

简单的步骤...

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

回答by ZZ-bb

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)?
• Are you trying to set the table PK to NULL on update (#5 in the list)?

• 您的外键索引在整个数据库中是否是唯一名称（列表中的#3）？
• 您是否尝试在更新时将表 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. Hope my link helps someone in the future thought.

编辑：我错过了对您原始帖子的评论。无符号/无符号 int 列可能会解决您的情况。希望我的链接可以帮助将来思考的人。

回答by Bill

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 cantset the column Sprache to NULL because it is defined as NOT NULL.

它不能将 Sprache 列设置为 NULL，因为它被定义为 NOT NULL。

回答by akelec

MySQL will execute this query:

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!

干杯!

回答by Kwed

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

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

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;
   

1. 更改您的表并为其添加索引..

   ALTER TABLE users ADD INDEX index_name (index_column)
   

2. 现在添加约束

   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;
   

Note if you don't add an index it wont work.

请注意，如果您不添加索引，它将无法工作。

After battling with it for about 6 hours I came up with the solution I hope this save a soul.

在与它斗争了大约 6 个小时后，我想出了解决方案，我希望这能拯救一个灵魂。

回答by Maksym Polshcha

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

1. 创建索引
2. 改变表

回答by manoj

try all in one query

在一个查询中尝试所有

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

回答by Pumudu Fernando

this is basically happens because your tables are in two different charsets. as a example one table created in charset=utf-8 and other tables is created in CHARSET=latin1 so you want be able add foriegn key to these tables. use same charset in both tables then you will be able to add foriegn keys. error 1005 foriegn key constraint incorrectly formed can resolve from this

这基本上是因为您的表在两个不同的字符集中。例如，在 charset=utf-8 中创建的一个表和在 CHARSET=latin1 中创建的其他表，因此您希望能够向这些表添加外键。在两个表中使用相同的字符集，然后您将能够添加外键。错误 1005 外键约束错误形成可以由此解决