mysql - 多个表的共享主键

Question

所以我有 3 个表:

神奇宝贝:

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| National_ID   | int(11)     | NO   | PRI | NULL    |       |
| Picture       | longblob    | YES  |     | NULL    |       |
| Name          | varchar(15) | NO   |     | NULL    |       |
| Generation_ID | int(11)     | NO   |     | NULL    |       |
| Type1         | varchar(8)  | NO   |     | NULL    |       |
| Type2         | varchar(8)  | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

统计数据:

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| National_ID     | int(11)     | NO   | PRI | NULL    |       |
| Health_Points   | int(11)     | NO   |     | NULL    |       |
| Attack          | int(11)     | NO   |     | NULL    |       |
| Defense         | int(11)     | NO   |     | NULL    |       |
| Special_Attack  | int(11)     | NO   |     | NULL    |       |
| Special_Defense | int(11)     | NO   |     | NULL    |       |
| Speed           | int(11)     | NO   |     | NULL    |       |
| Ability1        | varchar(20) | NO   |     | NULL    |       |
| Ability2        | varchar(20) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

其他:

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| National_ID  | int(11)     | NO   | PRI | NULL    |       |
| Evolves_From | varchar(15) | YES  |     | NULL    |       |
| Species      | varchar(20) | NO   |     | NULL    |       |
| Height_inch  | int(11)     | NO   |     | NULL    |       |
| Weight_lbs   | int(11)     | NO   |     | NULL    |       |
| Capture_Rate | int(11)     | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

我的问题是 Stats 表中的 National_ID 指向 Pokemon 表中的 National_ID，但我无法对 Misc 表执行相同操作。

每当我尝试在 Misc 中添加外键以指向 Pokemon 表时，都会出现重复错误。请帮助!谢谢

Answer 1

从错误消息来看，您似乎已经有一个名为 'FK_National_ID' 的外键约束。尝试将其更改为 'FK_Pokemon_National_ID' 或其他内容，它应该可以工作。

这是SQL Fiddle .