I am receiving these errors when creating my tables, they work when I take out the foreign keys. I am relatively new to the building of a database, so I may be missing something simple
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
);
These are the errors I get :
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 7
ERROR 1005 (HY000): Can't create table 'neClassCertForm.Contact_Info_Tbl' (errno: 150)
Ok the issue was a comma in the create table
Classroom_Address_Physical_Tbl (ID) on delete cascade,
which was breaking the table creation. Also add ENGINE=innodb to enforce the foreign key constraints.
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;