错误 1064 (42000) &&错误 1005 (HY000)： 无法创建表 Contact_Info_Tbl (errno： 150)

Question

我在创建表时收到这些错误，它们在我取出外键时工作。我对数据库的构建比较陌生，所以我可能错过了一些简单的

Create Table IF NOT EXISTS Classroom_Address_Mailing_Tbl (
ID int auto_increment primary key,                       
Street_Address varchar (25),
City varchar (25),
State_Abbr varchar (2),
Zipcode varchar (10)
);

Create Table IF NOT EXISTS Classroom_Address_Physical_Tbl (
ID int auto_increment primary key,
Street_Address varchar (25),
City varchar (25),
State_Abbr varchar (2),
Zipcode varchar (10) 
);
Create Table IF NOT EXISTS Classroom_Info_Tbl (
ID int auto_increment primary key,
School_Name varchar (25),
Classroom_Name_Offical varchar (25),
Classroom_URL varchar (25),
Classroom_Address_Physical_ID int,
Classroom_Address_Mailing_ID int,
foreign key (Classroom_Address_Mailing_ID) references 
Classroom_Address_Mailing_Tbl (ID) on delete cascade,
foreign key (Classroom_Address_Physical_ID) references 
Classroom_Address_Physical_Tbl (ID) on delete cascade,
);

Create Table IF NOT EXISTS Contact_Info_Tbl (
Classroom_ID int,
Contact_Name varchar (20),
Contact_Title varchar (20),
Contact_Email varchar (20),
Contact_Phone varchar (20),
foreign key (Classroom_ID) references 
Classroom_Info_Tbl (ID) on delete cascade
);

这些是我得到的错误:

ERROR 1064(42000):你的SQL语法有错误;查看与MySQL服务器版本对应的手册，了解在第7行

中使用')'附近的正确语法。

ERROR 1005 (HY000): Can't create table 'neClassCertForm.>Contact_Info_Tbl' (errno: 150)

Answer 1

问题在于create表中的逗号

Classroom_Address_Physical_Tbl (ID) on delete cascade,

正在破坏表的创建。还可以添加ENGINE=innodb来强制外键约束。

Create Table IF NOT EXISTS `Classroom_Address_Mailing_Tbl` (
ID int auto_increment primary key,                       
Street_Address varchar (25),
City varchar (25),
State_Abbr varchar (2),
Zipcode varchar (10)
)ENGINE=InnoDB;

Create Table IF NOT EXISTS `Classroom_Address_Physical_Tbl` (
ID int auto_increment primary key,
Street_Address varchar (25),
City varchar (25),
State_Abbr varchar (2),
Zipcode varchar (10) 
)ENGINE=InnoDB;
Create Table IF NOT EXISTS `Classroom_Info_Tbl` (
ID int auto_increment primary key,
School_Name varchar (25),
Classroom_Name_Offical varchar (25),
Classroom_URL varchar (25),
Classroom_Address_Physical_ID int,
Classroom_Address_Mailing_ID int,
foreign key (Classroom_Address_Mailing_ID) references 
Classroom_Address_Mailing_Tbl (ID) on delete cascade,
foreign key (Classroom_Address_Physical_ID) references 
Classroom_Address_Physical_Tbl (ID) on delete cascade
)ENGINE=InnoDB;

Create Table IF NOT EXISTS `Contact_Info_Tbl` (
Classroom_ID int,
Contact_Name varchar (20),
Contact_Title varchar (20),
Contact_Email varchar (20),
Contact_Phone varchar (20),
index ci (Classroom_ID),
foreign key (Classroom_ID) references 
Classroom_Info_Tbl (ID) on delete cascade
)ENGINE=InnoDB;