mysql - 无法使用外键添加新列

Question

我有一个 MySQL 表，现在我想添加一个包含主键和外键的新列。

到目前为止我已经尝试过

表格的外观:

CREATE TABLE IF NOT EXISTS `results` (
  `id` int(20) NOT NULL UNIQUE AUTO_INCREMENT,
  `resultA_id` int(20) NOT NULL,
  `resultB_id` int(20) NOT NULL,
  `resultC_id` int(20) NOT NULL,
  `resultD_id` int(20) NOT NULL,
  `resultE_id` int(20) NOT NULL,
  `resultF_id` int(20) NOT NULL,
  FOREIGN KEY (resultA_id) REFERENCES resultA(id),
  FOREIGN KEY (resultB_id) REFERENCES resultB(id),
  FOREIGN KEY (resultC_id) REFERENCES resultC(id),
  FOREIGN KEY (resultD_id) REFERENCES resultD(id),
  FOREIGN KEY (resultE_id) REFERENCES resultE(id),
  FOREIGN KEY (resultF_id) REFERENCES resultF(id),
  PRIMARY KEY (`resultA_id`, `resultB_id`, `resultC_id`, `resultD_id`, `resultE_id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

例如如何结果表看起来像

CREATE TABLE IF NOT EXISTS `resultA` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL UNIQUE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

现在我想向结果添加一个新列，该列与名为 table_test 的新表相关。因此我创建了一个新的 table_test

CREATE TABLE IF NOT EXISTS `table_test` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL UNIQUE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

然后向结果添加新列

ALTER TABLE results ADD COLUMN test_id;

现在我想添加主键

ALTER TABLE `results` ADD PRIMARY KEY(`test_id`);

我收到了这个错误

Cannot drop index 'PRIMARY': needed in a foreign key constraint

我也尝试在这里添加这样的外键

ALTER TABLE `results` ADD  CONSTRAINT `results_ibfk_7` FOREIGN KEY (`test_id`) REFERENCES `table_test`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

然后我收到了这个错误

Cannot add or update a child row: a foreign key constraint fails

我试图像这样忽略外键检查

SET FOREIGN_KEY_CHECKS=0

但这并没有帮助。

我还尝试使用新列创建一个新表，然后尝试将数据从旧表复制到新表

INSERT INTO results_new SELECT * FROM results;

我在这里也遇到了这个错误

Cannot add or update a child row: a foreign key constraint fails

我能做什么或者我做错了什么？

编辑我所做的就是现在。使用新列和外键等创建新表。然后将旧表转储到 CSV 文件。编辑此 CSV 文件并将新值添加到 csv 文件中的每一行。将此 CSV 文件导入到新表中。这有效!感谢大家的帮助!

Answer 1

我认为您想要做的是将 test_id 字段添加到表 results 的主键。

如上所述here ( source question )，您无法使用 SQLite 做到这一点。

如果您想使用 MySQL 执行此操作，则必须删除现有主键并重新创建它 ( source )。

ALTER TABLE MyTable
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (old_col1, old_col2, new_col);