MySQL "ERROR 1005 (HY000): Can't create table

Question

Would appreciate help. Can not create the section table.

CREATE DATABASE university;
use university;

CREATE TABLE classroom(
building VARCHAR(20), 
room_number INT,
capacity INT,
PRIMARY KEY (building,room_number)
) ;

CREATE TABLE Sales(
item VARCHAR(20),
color VARCHAR(20),
clothes_size VARCHAR(20),
quantity INT,
 PRIMARY KEY (item,color,clothes_size,quantity)
);

CREATE TABLE Department (
dept_name VARCHAR(20),
building VARCHAR(20),
budget INT,
FOREIGN KEY (building) references classroom(building),
PRIMARY KEY (dept_name,building,budget)
);

CREATE TABLE Course (
course_id VARCHAR(20)PRIMARY KEY,
title VARCHAR(20),
dept_name VARCHAR(20),
credits INT,
FOREIGN KEY (dept_name) references Department(dept_name)
);

CREATE TABLE Instructor(
ID VARCHAR(20) PRIMARY KEY,
neme VARCHAR(20),
dept_name VARCHAR(20),
salary INT,
FOREIGN KEY (dept_name) references Department(dept_name)
);

CREATE TABLE section(
course_id VARCHAR(20),
sec_id INT,
semester VARCHAR(20),
year1 INT,
building1 VARCHAR(20),
room_number INT,
time_slot_id CHAR,
FOREIGN KEY (course_id) REFERENCES Course(course_id),
FOREIGN KEY (building1) references classroom(building),
FOREIGN KEY (room_number) references classroom(room_number),
PRIMARY KEY(course_id,sec_id,semester,year1)
);

Error Code: 1005. Can't create table 'university.section' (errno: 150) — Or Nizri, May 03 '19 at 06:50
Possible duplicate of [MySQL "ERROR 1005 (HY000): Can't create table 'foo.#sql-12c\_4' (errno: 150)"](https://stackoverflow.com/questions/2799021/mysql-error-1005-hy000-cant-create-table-foo-sql-12c-4-errno-150) — Did, May 03 '19 at 07:07

score 0 · Accepted Answer · answered May 03 '19 at 06:56

Since you have two PK's on section table, you need to create one foreign key for both, not separate two:

CREATE TABLE section(
  course_id VARCHAR(20),
  sec_id INT,
  semester VARCHAR(20),
  year1 INT,
  building1 VARCHAR(20),
  room_number INT,
  time_slot_id CHAR,
  FOREIGN KEY (course_id) REFERENCES Course(course_id),
  FOREIGN KEY (building1,room_number) references classroom(building,room_number),
  PRIMARY KEY(course_id,sec_id,semester,year1)
);

MySQL "ERROR 1005 (HY000): Can't create table

1 Answers1