mysql - 错误代码 1215 : cannot add foreign key constraint

Question

我正在尝试添加外键约束，但失败了。我以前做过很多次，但我无法确定为什么要这样做。基本上，我想将 Skills 和 Employees 关联到 Skill_Bridge 表。我能够将 Skills 与 Skill_Bridge 相关联，但是当我尝试对 Employees 做同样的事情时，它失败了。数据类型相同，所以我不认为这是问题所在。我还尝试为 Skill_Bridge 创建一个主键，然后尝试将它们关联起来，但效果不佳。第一个约束是“FKey1”失败的约束。这是我的代码。任何帮助将不胜感激。提前致谢。

create database if not exists Q3;

use Q3;

drop table if exists Employees;

create table Employees( 
employeekey int not null, 
firstName varchar (100) not null,
lastName varchar (100) not null, 
employeeSkillGroupkey int not null,

primary key (employeekey) );

insert into Employees values 
(1, 'Ted', 'Codd', 1),
(2, 'Ralph' ,'Kimball', 7),
(3, 'Joe' ,'Celko',  1),
(4, 'James' ,'Gosling', 2),
(5, 'Godfrey', 'Muganda', 6),
(6, 'Margy', 'Ross', 5),
(7, 'Peter', 'Chen', 4),
(8, 'Terry' ,'Halpin',3),
(9, 'Tony', 'Morgan', 2);

drop table if exists Skills;

create table Skills(
empSkillKey int not null,
empSkillDescription varchar (1000) not null,
empSkillCategory varchar (200) not null,

primary key (empSkillkey));

insert into Skills values
(1, 'SQL', 'Database'),
(2, 'ERD', 'Database'),
(3, 'DM', 'Database'),
(4, 'Java', 'Programming'),
(5, 'Pascal', 'Programming');

drop table if exists Skill_Bridge;

create table Skill_Bridge(
employeeSkillGroupkey int not null,
empSkillKey int not null
);

insert into Skill_Bridge values
( 1, 1), 
( 2, 4),
( 3, 4),
( 3, 5),
( 4, 4),
( 4, 2),
( 5, 1),
( 5, 3),
( 6, 4),
( 6, 5),
( 6, 2),
( 7, 1),
( 7, 2),
( 7, 3),
( 7, 4);

ALTER TABLE Employees ADD CONSTRAINT FKey1 FOREIGN KEY (employeeSkillGroupkey)
REFERENCES Skill_Bridge (employeeSkillGroupkey)
ON DELETE Restrict
ON UPDATE Cascade;

ALTER TABLE Skill_Bridge ADD CONSTRAINT ForK2 FOREIGN KEY (empSkillKey)
REFERENCES Skills (empSkillKey)
ON DELETE Restrict
ON UPDATE Cascade;

Answer 1

您需要先在 skill_bridge 表中的 employeeSkillGroupkey 上创建一个索引，然后才能在外键中引用它，请参阅 foregn keys 上的 mysql 文档:

InnoDB permits a foreign key to reference any index column or group ofcolumns. However, in the referenced table, there must be an indexwhere the referenced columns are listed as the first columns in thesame order.

您可能还想反转外键的方向。连接表应该引用主表，而不是相反。