MySQL - ＃1005 - 无法创建表'university.Enrolment'（errno：150）（详情...）

Question

我不知道为什么它不会让我创建表注册。我只需要引用学生，uniadmin和课程，并且我已经在注册之前创建了它们。

它们都是相同的名称，属性类型和长度，所以我不知道这里有什么不正确。这发生在我在...中使用外键引用的所有表中，所以它必须与我所假设的有关。

错误我收到： SQL查询：

CREATE TABLE Enrolment(

EnrolmentIDCHAR(3) NOT NULL , 
StudentIDCHAR(3) NOT NULL , 
StaffIDCHAR(3) NOT NULL , 
CourseIDCHAR(7) NOT NULL , 
CampusCHAR(2) NOT NULL , 
Trimester TINYINT(1) NOT NULL , 
CONSTRAINT PK_Enrolment PRIMARY KEY (EnrolmentID) , 
CONSTRAINT FK1_Enrolment FOREIGN KEY (StudentID) REFERENCES Student(StudentID) , 
CONSTRAINT FK2_Enrolment FOREIGN KEY (StaffID) REFERENCES UniversityAdmin(StaffID) , 
CONSTRAINT FK3_Enrolment FOREIGN KEY (CourseID) REFERENCES Course(CourseID) , 
CONSTRAINT FK4_Enrolment FOREIGN KEY (Campus) REFERENCES Course(Campus) , 
CONSTRAINT FK5_Enrolment FOREIGN KEY (Trimester) REFERENCES Course(Trimester) /*constraint Check_EnrolmentCampus check (Campus in ('GC', 'BR')*/ 
); 

MySQL said: Documentation 

#1005 - Can't create table 'university.Enrolment' (errno: 150) (Details…) 

“详细信息” 说：

InnoDB的文档

支持事务，行级锁和外键

[变量|缓冲池| InnoDB的状态]

我的代码：

DROP DATABASE IF EXISTS university; 
CREATE DATABASE IF NOT EXISTS university; 
USE university; 

drop table if exists Student; 
drop table if exists UniversityAdmin; 
drop table if exists Course; 
drop table if exists Academic; 
drop table if exists Teaches; 
drop table if exists Administers; 
drop table if exists Manages; 
drop table if exists Enrolment; 
drop table if exists Scored; 

Create table Student(
      StudentID Char(3) not null, 
      Password Varchar(20) not null, 
      FirstName Varchar(15) not null, 
      MiddleName Varchar(15), 
      LastName Varchar(15) not null, 
      DateOfBirth Date not null, 
      Sex Char(1) not null, 
      HomeAddress Varchar(60) not null, 
      PhoneNumber Varchar(10) not null, 
      constraint PK_Student primary key(StudentID) 
      /*constraint Check_StudentSex check (Sex in ('M', 'F')*/ 
      ); 

Create table UniversityAdmin(
      StaffID Char(3) not null, 
      Password Varchar(20) not null, 
      Duty Varchar(20) not null, 
      FirstName Varchar(15) not null, 
      MiddleName Varchar(15), 
      LastName Varchar(15) not null, 
      DateOfBirth Date not null, 
      Sex Char(1) not null, 
      HomeAddress Varchar(60) not null, 
      PhoneNumber Varchar(10) not null, 
      constraint PK_UniversityAdmin primary key(StaffID) 
      /*constraint Check_UniversityAdminSex check (Sex in ('M', 'F')*/ 
      ); 

Create table Course(
      CourseID Char(7) not null, 
      Campus Char(2) not null, 
      Trimester TinyInt(1) not null, 
      CourseName Varchar(50) not null, 
      Convenor Varchar(30) not null, 
      Prerequisite Char(7), 
      Year TinyInt(4) not null, 
      constraint PK_Course primary key(CourseID, Campus, Trimester) 
      /*constraint Check_CourseCampus check (Campus in ('GC', 'BR')*/ 
      ); 

Create table Academic(
      StaffID Char(3) not null, 
      Password Varchar(20) not null, 
      Position Varchar(30) not null, 
      FirstName Varchar(15) not null, 
      MiddleName Varchar(15), 
      LastName Varchar(15) not null, 
      DateOfBirth Date not null, 
      Sex Char(1) not null, 
      HomeAddress Varchar(60) not null, 
      PhoneNumber Varchar(10) not null, 
      constraint PK_Academic primary key(StaffID) 
      /*constraint Check_AcademicSex check (Sex in ('M', 'F')*/ 
      );  

Create table Enrolment(
      EnrolmentID Char(3) not null, 
      StudentID Char(3) not null, 
      StaffID Char(3) not null, 
      CourseID Char(7) not null, 
      Campus Char(2) not null, 
      Trimester TinyInt(1) not null, 
      constraint PK_Enrolment primary key (EnrolmentID), 
      constraint FK1_Enrolment foreign key (StudentID) references Student(StudentID), 
      constraint FK2_Enrolment foreign key (StaffID) references UniversityAdmin(StaffID), 
      constraint FK3_Enrolment foreign key (CourseID) references Course(CourseID), 
      constraint FK4_Enrolment foreign key (Campus) references Course(Campus), 
      constraint FK5_Enrolment foreign key (Trimester) references Course(Trimester) 
      /*constraint Check_EnrolmentCampus check (Campus in ('GC', 'BR')*/ 
      ); 

Answer 1

如果您已经创建了其他表，你可以改变Course表是这样的：

ALTER TABLE Course 
     ADD INDEX(Campus), 
     ADD INDEX(Trimester); 

这样你就可以使用这些两列像国外键在您的Enrolment表中，否则在创建此表时会出错。

如果你想删除表Course你必须先删除Enrolment，然后再添加外键。