MySQL "ERROR 1005 (HY000): Can' t 创建表

Question

非常感谢帮助。无法创建节表。

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)
);

Answer 1

由于节表上有两个 PK，因此您需要为两者创建一个外键，而不是单独的两个:

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)
);