Error Code 1215 MySQL

Below is my schema, everything runs except for the Assessment table, where I get an error 1215. cannot add foreign key constraint. Thanks in advance. I have also referenced the primary keys of other tables as the foreign keys in the Assessment table.

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)
);

Below is the rror I am getting for SemesterID, TeamID, ProjectID and AssessmentTypeID:

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.

Any help is appreciated.

---

When you're making a foreign key to a table with a composite primary key (ie a key of multiple columns), you should make the foreign key composite as well.

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)
);

I answered a very similar question here: SQL Can't create table (errno: 150)

链接地址: http://www.djcxy.com/p/86278.html

上一篇: MySql和创建外键

下一篇: 错误代码1215 MySQL