错误代码1215：无法添加外键约束

I am trying to add a foreign key constraint, but it fails. I did that many times before and I can not determine why it is doing that. Basically, I want to relate Skills and Employees to the Skill_Bridge table. I was able to relate Skills with the Skill_Bridge, however when I try to do the same with Employees it fails. The data types are the same, so I do not think this is the problem. I also tried to creating a primary key for the Skill_Bridge, and then try to relate them and it did not work as well. The first constraint is the one that fails 'FKey1'. This is my code. Any help will be appreciated. Thanks in advance.

我试图添加一个外键约束，但它失败了。我以前做了很多次，我无法确定它为什么这样做。基本上，我想将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;

1 个解决方案

#1

You need to create an index on employeeSkillGroupkey in skill_bridge table before you can reference it in a foreign key, see mysql documentation on foregn keys:

您需要在skill_bridge表中的employeeSkillGroupkey上创建索引，然后才能在外键中引用它，请参阅有关foregn键的mysql文档：

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

InnoDB允许外键引用任何索引列或列组。但是，在引用的表中，必须有一个索引，其中引用的列被列为相同顺序的第一列。

You may also want to reverse the direction of the foreign key. The connection table should reference the master table, not vice versa.

您可能还想要反转外键的方向。连接表应引用主表，反之亦然。

#1