错误代码1215 MySQL

Question

下面是我的模式，除了评估表之外，所有东西都在运行，其中我得到了一个错误1215。无法添加外键约束。提前谢谢。我还引用了其他表的主键作为评估表中的外键。

create database if not exists tcsdb;
use tcsdb;

create table if not exists Person (
PersonID int not null auto_increment,
ID varchar(10) not null unique,
FirstName varchar(45) not null,
LastName varchar(45) not null,
UserName varchar(45) not null unique,
Password varchar(45) not null,
Email varchar(45) not null,
ContactNo varchar(45) not null,
primary key(PersonID)
);

create table if not exists Role (
RoleID int not null auto_increment,
RoleTitle varchar(45),
RoleDescription varchar(45),
primary key(RoleID)
);

create table if not exists Semester (
SemesterID int not null auto_increment,
SemesterPeriod varchar(45),
SemesterYear date,
primary key(SemesterID)
);

create table if not exists Unit (

UnitID int not null auto_increment,
SemesterID int,
UnitCode varchar(10) not null unique,
UnitName varchar(45) not null unique,
UnitDescription varchar(45) not null unique,
foreign key(SemesterID) references Semester(SemesterID) on update cascade on delete        restrict,    
primary key(UnitID, SemesterID)

);

create table if not exists Team (

TeamID int not null auto_increment,
TeamCode varchar(45) not null unique,
TeamName varchar(45) not null,
primary key(TeamID)

);

create table if not exists Project (

ProjectID int not null auto_increment,
ProjectCode varchar(45) not null unique,
ProjectDescription varchar(45) not null,
primary key(ProjectID)

);

create table if not exists AssessmentType (
AssessmentTypeID int not null auto_increment,
AssessmentType varchar(45) not null unique,
primary key(AssessmentTypeID)
);



create table if not exists Assessment (

AssessmentID int not null auto_increment,
PersonID int,
UnitID int,
SemesterID int,
TeamID int ,
ProjectID int ,
AssessmentComments varchar(45) not null,
AssessmentDueDate date,
AssessmentDateSubmitted date,
AssessmentTypeID int,
foreign key(PersonID) references PersonUnit(PersonID) on update cascade on delete restrict, 
foreign key(UnitID) references PersonUnit(UnitID) on update cascade on delete restrict,     
foreign key(SemesterID) references PersonUnit(SemesterID) on update cascade on   delete restrict, 
foreign key(TeamID) references Team(TeamID) on update cascade on delete restrict, 
foreign key(ProjectID) references Project(ProjectID)on update cascade on delete restrict,
foreign key(AssessmentTypeID) references AssessmentType(AssessmentTypeID) on update cascade on delete restrict,
primary key(AssessmentID, PersonID, UnitID, SemesterID, TeamID, ProjectID,   AssessmentTypeID)
);


create table if not exists PersonRole (
PersonID int,
RoleID int,
primary key(PersonID, RoleID),
foreign key(PersonID) references Person(PersonID) on update cascade on delete restrict,
foreign key(RoleID) references Role(RoleID) on update cascade on delete restrict
);


create table if not exists PersonUnit (
PersonID int,
UnitID int,
SemesterID int,
foreign key(PersonID) references Person(PersonID) on update cascade on delete restrict,
foreign key(UnitID, SemesterID) references Unit(UnitID, SemesterID) on update cascade on delete restrict,
primary key(PersonID, UnitID, SemesterID)
);

下面是我为SemesterID、TeamID、ProjectID和AssessmentTypeID收到的rror：

无法在引用的表中找到索引，其中引用的列显示为第一列，或表中的列类型和引用的表不匹配约束。注意，在用>= InnoDB4.1.12创建的表中更改了ENUM和SET的内部存储类型，新表中的此类列不能引用旧表中的此类列。

任何帮助都是非常感谢的。

Answer 1

当您使用复合主键(即多列键)为表创建外键时，也应该将外键组合起来。

create table if not exists Assessment (
AssessmentID int not null auto_increment,
PersonID int,
UnitID int,
SemesterID int,
TeamID int ,
ProjectID int ,
AssessmentComments varchar(45) not null,
AssessmentDueDate date,
AssessmentDateSubmitted date,
AssessmentTypeID int,
foreign key(PersonID,UnitID,SemesterID) references PersonUnit(PersonID,UnitID,SemesterID) on update cascade on delete restrict,
restrict, 
foreign key(TeamID) references Team(TeamID) on update cascade on delete restrict,
foreign key(ProjectID) references Project(ProjectID)on update cascade on delete restrict,
foreign key(AssessmentTypeID) references AssessmentType(AssessmentTypeID) on update cascade on delete restrict,
primary key(AssessmentID, PersonID, UnitID, SemesterID, TeamID, ProjectID,   AssessmentTypeID)
);

我在这里回答了一个非常类似的问题：SQL Can't create table (errno: 150)