mysql - 将同一个外键添加到一个表的96列

Question

我有一个看起来像这样的表:

CREATE TABLE `library_mix` (
  `lib_mix_id` VARCHAR(64) NOT NULL ,
  `date` DATE NOT NULL ,
  `index1` VARCHAR(64) NULL ,
  `index2` VARCHAR(64) NULL ,
  `index3` VARCHAR(64) NULL ,
  `index4` VARCHAR(64) NULL ,
  `index5` VARCHAR(64) NULL ,
 ...
  `index96` VARCHAR(64) NULL ,
 ...
)

对于 index1 到 index96 中的每一个，我想添加相同的外键约束。理想情况下，我想在编程循环中同时创建索引和 fk 约束，但我不清楚如何在 MySQL 中执行此操作，也没有找到任何好的示例。像这样的东西:

CREATE TABLE `library_mix` (
  `lib_mix_id` VARCHAR(64) NOT NULL ,
  `date` DATE NOT NULL ,

for i = 1 to 96
  `index`$i VARCHAR(64) NULL,
end
...
for i = 1 to 96
  CONSTRAINT `fk_library_mix_lib_id`$i
  FOREIGN KEY (`lib_id` )
  REFERENCES `library` (`lib_id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)
end)

有没有一种简单的方法可以做到这一点，而不是将所有内容都输入 96 次？

编辑

我对下面一些评论的回答太长了，所以我把它加在这里。

• 上面的术语“图书馆”指的是 DNA 片段的物理集合(在试管中)(我认为图书馆是一个可怕的术语，但我没有编造它并坚持使用它)。
• 每个文库的每个片段(也由 DNA 制成)都附有条形码，并与其他 DNA 文库混合(只要这些文库具有不同的条形码)
• 有 96 种可能的条形码(此处由“index1”到“index96”标识)。

因此，在此表中，我尝试对 DNA 文库的特定组合进行建模。外键约束是为了确保库 ID 存在于库表中。

可能有用的其他信息:

• 不同的 DNA 文库可以有相同的条形码(因此不能混合在一起)
• 一个 DNA 文库可以存在于多个混合物中
• 单个 DNA 样本可以有多个 DNA 文库(具有相同或不同的条形码)

我很高兴听到比每个条形码一列更好的建模方法。

谢谢!

凯文

编辑 2@eggyal 在评论中的建议是处理此问题的正确方法。 (我的新手错误。)谢谢!

Answer 1

我将回答您的问题，但您似乎在图书馆之间存在多对多关系，而您没有正确解决它。 eggyal 和 Thomas 的评论非常有道理。请研究如何在数据库中创建适当的一对多和多对一关系。这将使这个问题变得容易得多。

要轻松创建约束，您可以打开与 MySQL 服务器的连接并执行以下操作:

SELECT  CONCAT("CONSTRAINT `fk_library_mix_lib_id`",COLUMN_NAME,"
    FOREIGN KEY (`",COLUMN_NAME,"` )
    REFERENCES `library` (`lib_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION ") AS SQLStatement
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_NAME ="library_mix"
AND COLUMN_NAME LIKE "index%"

您可以将结果复制并粘贴到新的查询窗口中并执行。我没有测试生成的语法，但是您应该能够以最小的努力修改查询以创建正确的 SQL 命令字符串。我将此作为练习留给您，因为它肯定会增强您对 MySQL 数据字典的了解。基本上去阅读 MySQL 中的 INFORMATION_SCHEMA View ，它们使这样的事情变得轻而易举。