MySQL表重复键错误求助：多表外键关联异常排查

问题描述 #

作为SQL新手，尝试创建Analyst、SupportTeam、TeamMembership、TeamSkills四张表并插入数据，其中TeamMembership与TeamSkills的外键均指向SupportTeam的主键SupportTeamName。执行代码时，先出现TeamSkills表重复键错误；调换表创建顺序使TeamMembership在最后时，错误转移至该表；移除任一外键代码可正常运行，但需保留外键实现表关联。

错误原因 #

问题出在外键约束的命名重复。数据库中约束名称是全局唯一的，你给TeamMembership和TeamSkills的外键都设置了相同的名称FK_SupportTeamName，当创建第二个外键时，数据库发现同名约束已存在，就会抛出重复约束的错误。

解决方案 #

给两个外键约束分配唯一的名称，避免命名冲突。比如：

• TeamMembership的外键命名为FK_TeamMembership_SupportTeam
• TeamSkills的外键命名为FK_TeamSkills_SupportTeam

修正后的完整代码 #

CREATE TABLE Analyst (
    AnalystID VARCHAR(255),
    First_Name VARCHAR(255),
    Last_Name VARCHAR(255),
    Tel_No VARCHAR(255),
    Email_Address VARCHAR(255),
    CONSTRAINT PK_AnalystID PRIMARY KEY
    (AnalystID)
    );
 
INSERT INTO Analyst (AnalystID,First_Name,Last_Name,Tel_No,Email_Address)
VALUES ('DID10000','Jimi','Hendrix','01632 504881','jhendrix@businesscorp.co.uk'),
       ('DID10001','Kurt','Cobain','01632 678231','kcobain@businesscorp.co.uk'),
       ('DID10002','Amy','Winehouse','01632 001992','awinehouse@businesscorp.co.uk'),
       ('DID10003','Janis','Joplin','01632 112642','jjoplin@businesscorp.co.uk'),
       ('DID10004','Jim','Morrison','01632 913912','jmorrison@businesscorp.co.uk'),
       ('DID10005','Brian','Jones','01632 868712','bjones@businesscorp.co.uk'),
       ('DID10006','Freddie','Mercury','01632 963126','fmercury@businesscorp.co.uk'),
       ('DID10007','John','Lennon','01632 990112','jlennon@businesscorp.co.uk');

 
CREATE TABLE SupportTeam (
    SupportTeamName VARCHAR(255),
  
    CONSTRAINT PK_SupportTeamName PRIMARY KEY
    (SupportTeamName)
    );
 
INSERT INTO SupportTeam (SupportTeamName)
VALUES ('Desktop Services'),
       ('Network and Comms'),
       ('Servicedesk'),
       ('O365 Services');
       
CREATE TABLE TeamMembership (
    TeamMember int NOT NULL AUTO_INCREMENT,
    SupportTeamName VARCHAR(255), 
    AnalystID VARCHAR(255),
    CONSTRAINT PK_TeamMember PRIMARY KEY (TeamMember),
    -- 修改外键约束名称为唯一值
    CONSTRAINT FK_TeamMembership_SupportTeam FOREIGN KEY
    (SupportTeamName) REFERENCES SupportTeam (SupportTeamName),
    CONSTRAINT FK_AnalystID FOREIGN KEY
    (AnalystID) REFERENCES Analyst (AnalystID)
    );
 
INSERT INTO TeamMembership (SupportTeamName,AnalystID)
VALUES ('Desktop Services','DID10000'),
       ('Desktop Services','DID10003'),
       ('Desktop Services','DID10007'),
       ('Desktop Services','DID10001'),
       ('Network and Comms','DID10002'),
       ('Network and Comms','DID10004'),
       ('Network and Comms','DID10003'),
       ('Servicedesk','DID10004'),
       ('Servicedesk','DID10005'),
       ('Servicedesk','DID10006'),
       ('O365 Services','DID10002'),
       ('O365 Services','DID10007');
    


CREATE TABLE TeamSkills (
    SkillID int NOT NULL AUTO_INCREMENT,
    SupportTeamName VARCHAR(255),
    Skill VARCHAR(255),
    CONSTRAINT PK_SkillID PRIMARY KEY (SkillID),
    -- 修改外键约束名称为唯一值
    CONSTRAINT FK_TeamSkills_SupportTeam FOREIGN KEY
    (SupportTeamName) REFERENCES SupportTeam (SupportTeamName)
    );
    
INSERT INTO TeamSkills (SupportTeamName,Skill)
VALUES ('Desktop Services','Software Install'),
       ('Desktop Services' ,'System Updates'),
       ('Desktop Services', 'Hardware Repair'),
       ('Network and Comms','Network Hardware Installation'),
       ('Network and Comms','Connectivity Issues'),
       ('Network and Comms','VPN Problems'),
       ('Servicedesk','Basic Troubleshooting'),
       ('Servicedesk','Remote Equipment Setup'),
       ('Servicedesk','Provisioning Basic Equipment'),
       ('O365 Services','Mobile Phones'),
       ('O365 Services','Outlook'),
       ('O365 Services','Azure Services');

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