如何创建主键相同且其中一个主键兼作外键的两张数据库表？

你的建表逻辑存在方向错误，以下是问题分析和正确的实现方案：

问题核心 #

你搞反了主表与关联表的依赖关系：artist是存储艺术家信息的主表，artartist应该是艺术家与作品的多对多关联表，而非主表。

原代码的问题 #

1. artartist的artistID设为自增主键完全不合理，关联表的作用是绑定两个主表的关系，不需要自增主键（或可设单独自增ID，但核心是外键关联）。
2. artist表的外键指向artartist是逻辑倒置：应该是关联表引用主表的主键，而非主表依赖关联表。
3. artist表的artistID应该设为自增，作为艺术家的唯一标识。

正确的建表语句 #

1. 先创建艺术家主表（artist） #

CREATE TABLE artist (
    artistID MEDIUMINT NOT NULL AUTO_INCREMENT,
    Lname varchar(30),
    Fname varchar(30),
    PRIMARY KEY (artistID)
);

2. 创建艺术家-作品关联表（artartist） #

假设你已经有artwork作品表（若没有需先创建）：

CREATE TABLE artartist (
    artistID MEDIUMINT NOT NULL,
    artworkID MEDIUMINT NOT NULL,
    -- 用联合主键避免同一艺术家与作品重复关联
    PRIMARY KEY (artistID, artworkID),
    -- 外键关联艺术家表
    FOREIGN KEY (artistID) REFERENCES artist(artistID),
    -- 外键关联作品表
    FOREIGN KEY (artworkID) REFERENCES artwork(artworkID)
);

最佳实践说明 #

• 主表（artist、artwork）各自维护自增主键，存储核心实体信息。
• 关联表（artartist）仅存储两个主表的主键作为外键，实现多对多关联，无多余无用字段。
• 联合主键保证关联关系的唯一性，避免重复数据。

内容的提问来源于stack exchange，提问作者Zzen