MySQL - #1005 - 无法创建表 'university.Enrolment'(错误号 : 150) (Details…))

Question

我不知道为什么它不允许我创建表注册。我只需要引用学生、uniadmin 和类(class)，我已经在注册上方创建了它们。

它们的名称、属性类型和长度都相同，所以我不知道这里有什么问题。我在其中使用外键引用的所有表都发生这种情况......所以我认为这一定与它们有关。

我收到的错误: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。