mysql - 使用外键约束更改 MySQL 表

Question

有两个表 news 和 language。这是 MySQL 中的原始表:

CREATE TABLE `news` (
`news_id` int(11) NOT NULL AUTO_INCREMENT,
`fleet_id` int(11) NOT NULL,
`channel_id` int(11) NOT NULL,
`effective_from` date NOT NULL,
`effective_to` date NOT NULL,
`news` text,
PRIMARY KEY (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `language` (
`ietf_language_tag` varchar(5) NOT NULL,
`endonym` varchar(32) NOT NULL,
`exonym` varchar(32) NOT NULL,
 PRIMARY KEY (`ietf_language_tag`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* 发出 alter table 命令 */

ALTER TABLE news
ADD ietf_language_tag varchar(5) NOT NULL,
ADD KEY (ietf_language_tag) ,
ADD CONSTRAINT news_fkey1 FOREIGN KEY (ietf_language_tag) REFERENCES   
language(ietf_language_tag) ON DELETE NO ACTION  ON UPDATE NO ACTION ;

/* 出现错误 */

错误:

14:57:51 ALTER TABLE news ADD ietf_language_tag varchar(5) NOT NULL, ADD KEY
(ietf_language_tag) , ADD CONSTRAINT news_fkey1 FOREIGN KEY (ietf_language_tag)
REFERENCES language(ietf_language_tag) ON DELETE NO ACTION ON UPDATE NO ACTION
Error Code: 1215. Cannot add foreign key constraint 0.437 sec

Answer 1

字符集问题

两者news和language有不同的字符集latin1对于 news , utf8对于 language .

FOREIGN_KEY_CHECKS

即使字符集相同，如果news上有记录，您也会收到错误消息如下表。

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db_name.#sql-296_72a7a, CONSTRAINT news_fkey1 FOREIGN KEY (ietf_language_tag) REFERENCES language (ietf_language_tag) ON DELETE NO ACTION ON UPDATE NO ACTION)

这个问题可以用 SET FOREIGN_KEY_CHECKS = 0; 来解决

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE news
    -> ADD ietf_language_tag varchar(5) NOT NULL,
    -> ADD KEY (ietf_language_tag) ,
    -> ADD CONSTRAINT news_fkey1 FOREIGN KEY (ietf_language_tag) REFERENCES
    -> language(ietf_language_tag) ON DELETE NO ACTION  ON UPDATE NO ACTION ;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0