MySQL 错误 150

我正在创建一些简单的表，但我无法通过这个外键错误，我不知道为什么.这是下面的脚本.

创建 TABLE 教师 (ID varchar(10),First_Name varchar(50) 非空，姓氏 varchar(50) 非空，主键(ID));创建表课程(Course_Code varchar(10),标题 varchar(50) NOT NULL,主键(课程代码));创建表部分(Index_No int,Course_Code varchar(10),Instructor_ID varchar(10),PRIMARY KEY (Index_No),FOREIGN KEY (Course_Code) REFERENCES 课程(Course_Code)删除级联在更新级联上，外键 (Instructor_ID) REFERENCES Instructors(ID)ON DELETE 设置默认值);

<块引用>

错误代码:1005.无法创建表336_project.sections"(错误号:150)

我的数据类型似乎相同，语法似乎正确.谁能指出我在这里没有看到的内容?

我使用的是 MySQL Workbench 5.2

解决方案

如果您使用的是 InnoDB 引擎，ON DELETE SET DEFAULT 就是您的问题.这是手册的摘录:

<块引用><块引用>

虽然 SET DEFAULT 被 MySQL 服务器允许，但它被 InnoDB 拒绝为无效.InnoDB 表不允许使用此子句的 CREATE TABLE 和 ALTER TABLE 语句.

您可以使用ON DELETE CASCADE 或ON DELETE SET NULL，但不能使用ON DELETE SET DEFAULT.有更多信息这里.>

I'm creating a few simple tables and I can't get passed this foreign key error and I'm not sure why. Here's the script below.

create TABLE Instructors (

ID varchar(10),
First_Name varchar(50) NOT NULL,
Last_Name varchar(50) NOT NULL,
PRIMARY KEY (ID)
);

create table Courses (

Course_Code varchar(10),
Title varchar(50) NOT NULL,
PRIMARY KEY (Course_Code)

);


create table Sections (
Index_No int,
Course_Code varchar(10),
Instructor_ID varchar(10),
PRIMARY KEY (Index_No),
FOREIGN KEY (Course_Code) REFERENCES Courses(Course_Code)
    ON DELETE cascade
    ON UPDATE cascade,
FOREIGN KEY (Instructor_ID) REFERENCES Instructors(ID)
    ON DELETE set default

);

Error Code: 1005. Can't create table '336_project.sections' (errno: 150)

My data types seem identical and the syntax seems correct. Can anyone point out what I'm not seeing here?

I'm using MySQL Workbench 5.2

解决方案

If you're using the InnoDB engine, the ON DELETE SET DEFAULT is your problem. Here's an excerpt from the manual:

While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE and ALTER TABLE statements using this clause are not allowed for InnoDB tables.

You can use ON DELETE CASCADE or ON DELETE SET NULL, but not ON DELETE SET DEFAULT. There's more information here.