mysql - 如何防止连接表中的组合或排列？

Question

我在 MySQL Workbench 中有以下表格：MdV、MdV_has_Chain 和 Chain。

describe MdV
+------------+--------------+------+-----+-------------+-------+
| Field      | Type         | Null | Key | Default     | Extra |
+------------+--------------+------+-----+-------------+-------+
| MdVID      | varchar(255) | NO   | PRI | NULL        |       |
| Source     | longtext     | YES  |     | NULL        |       |
| Chains     | int unsigned | NO   |     | 0           |       |
| CampaignID | varchar(255) | NO   | MUL | No_Campaign |       |
+------------+--------------+------+-----+-------------+-------+
describe MdV_has_Chain
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| MdVID     | varchar(255) | NO   | PRI | NULL    |       |
| ChainID   | varchar(255) | NO   | PRI | NULL    |       |
| Chain_Num | int unsigned | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
describe Chain
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ChainID   | varchar(255) | NO   | PRI | NULL    |       |
| Positions | int unsigned | NO   |     | 0       |       |
| VectorID  | varchar(255) | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

它们在 EER 图中。目前，感兴趣的表包含以下模拟数据：

select * from mdv;
+-------------+-----------------+--------+-------------+
| MdVID       | Source          | Chains | CampaignID  |
+-------------+-----------------+--------+-------------+
| ITS058-M152 |                 |      1 | C7          |
| ITS058-M182 |                 |      2 | No_Campaign |
| ITS058-M244 | Rational Design |      1 | C16         |
| ITS058-M253 | Rational Design |      2 | C17         |
| ITS058-M258 |                 |      1 | No_Campaign |
| TEST        |                 |      0 | No_Campaign |
+-------------+-----------------+--------+-------------+
select * from mdv_has_chain;
+-------------+------------+----------------+
| MdVID       | ChainID    | chain_position |
+-------------+------------+----------------+
| ITS058-M152 | ITS058-Ch1 |              1 |
| ITS058-M182 | ITS058-Ch2 |              1 |
| ITS058-M182 | ITS058-Ch3 |              2 |
| ITS058-M244 | ITS058-Ch4 |              1 |
| ITS058-M253 | Ch1        |              2 |
| ITS058-M253 | ITS058-Ch5 |              1 |
| ITS058-M258 | ITS058-Ch6 |              1 |
+-------------+------------+----------------+
select * from chain;
+------------+-----------+-------------+
| ChainID    | Positions | VectorID    |
+------------+-----------+-------------+
| Ch1        |         2 | T343        |
| ITS058-Ch1 |         7 | ITS058-V240 |
| ITS058-Ch2 |         7 | ITS058-V278 |
| ITS058-Ch3 |         1 | R208        |
| ITS058-Ch4 |         6 | ITS058-V352 |
| ITS058-Ch5 |         7 | ITS058-V361 |
| ITS058-Ch6 |         6 | ITS058-V366 |
+------------+-----------+-------------+

要查看每个 MdV 有哪些链，我使用了以下查询：

select mdv.mdvid, group_concat(mdv_has_chain.chainid order by mdv_has_chain.chain_num) as chainid, group_concat(mdv_has_chain.chain_num order by mdv_has_chain.chain_num) as chain_position from mdv inner join mdv_has_chain on mdv.mdvid = mdv_has_chain.mdvid group by mdv_has_chain.mdvid
+-------------+-----------------------+----------------+
| mdvid       | chainid               | chain_position |
+-------------+-----------------------+----------------+
| ITS058-M152 | ITS058-Ch1            | 1              |
| ITS058-M182 | ITS058-Ch2,ITS058-Ch3 | 1,2            |
| ITS058-M244 | ITS058-Ch4            | 1              |
| ITS058-M253 | ITS058-Ch5,Ch1        | 1,2            |
| ITS058-M258 | ITS058-Ch6            | 1              |
+-------------+-----------------------+----------------+

我想知道如何防止将相同的 ChainId 或它们的组合输入到不同的 MdVID 中？例如，我将如何防止以下插入工作：

insert into mdv_has_chain (mdvid, chainid, chain_num) values ("TEST", "ITS058-Ch2", 2), ("TEST", "ITS058-Ch3", 1)

ITS058-M182已经有这些 ChainID，但在不同的位置。请注意，一个 MdV 可以有 1 到 N 个链。同样，我将如何允许 ChainIds 在不同位置的排列，但防止输入具有相同位置的条目？我不打算同时实施这两件事。我只是想知道我将如何单独实现。

谢谢你的时间。任何帮助表示赞赏。

标签： mysql