Error Code 1005. Can't create table : "takes" and "Teaches"

Question

Would appreciate help. Can not create the "Teaches" and "takes" 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,room_number) references classroom(building,room_number),
  PRIMARY KEY(course_id,sec_id,semester,year1)
);

CREATE TABLE Teaches(
ID VARCHAR(20),
course_id VARCHAR(20),
sec_id VARCHAR(20),
semester VARCHAR(20),
year1 INT,
FOREIGN KEY (ID) references Instructor(ID),
FOREIGN KEY (sec_id) references section(sec_id),
FOREIGN KEY (semester,year1) references section(semester,year1),
FOREIGN KEY (course_id) references course(course_id),
PRIMARY KEY (ID,course_id,sec_id,year1,semester)
);

CREATE TABLE student(
ID VARCHAR(20) PRIMARY KEY,
name1 VARCHAR(20),
dept_name VARCHAR(20),
tot_cred INT);

CREATE TABLE Takes(
ID VARCHAR(20), 
course_id VARCHAR(20),
sec_id INT,
semester VARCHAR(20),
year1 INT,
grade char,
FOREIGN KEY (ID) references student(ID),
FOREIGN KEY (sec_id) references section(sec_id),
FOREIGN KEY (semester) references section(semester),
FOREIGN KEY (year1) references section(year1),
FOREIGN KEY (course_id) references course(course_id),
PRIMARY KEY(ID,sec_id,semester,year1,course_id)
);

Answer 1

Foreign key references need to have the same types as the primary keys they refer to. So consider:

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 Instructor (
    ID VARCHAR(20) PRIMARY KEY,
    neme VARCHAR(20),
    dept_name VARCHAR(20),
    salary INT,
    FOREIGN KEY (dept_name) references Department(dept_name)
);

The primary key for Department has three components -- dept_name, building, budget. However, the reference has only one component. They cannot match; the number is not correct, much less the types.

I would recommend that you use auto-incremented ids for your primary keys. You can also specify uniqueness constraints, if you like. So for this small example:

create table buildings (
    building_id int auto_increment primary key,
    building varchar(20),
    constraint unq_buildings_building unique(building)
);

create table classrooms (
    classroom_id int auto_increment primary key,
    building_id int,
    room_number int,
    capacity int,
    constraint fk_classrooms_building_id foreign key (building_id) references buildings (building_id)
) ;

create table Departments (
    department_id int auto_increment primary key,
    dept_name varchar(20),
    building_id int,
    budget INT,
    constraint fk_departments_building_id foreign key (building_id) references buildings(building_id)
);

create table instructors (
    instructor_id int auto_increment primary key,
    id varchar(20) unique,
    name varchar(20),
    department_id int,
    salary int,
    foreign key instructors_department_id foreign key (department_id) references departments (department_id)
);

This is just a small sample of your tables. But the following are some rules that I follow:

• Table names are plural.
• They have a primary key which is the singular form followed by _id (or Id).
• Foreign key references are only to primary keys.
• To the extent possible, foreign keys and primary keys have the exact same name.
• Explicit constraints are given names. The names follow a very precise naming convention.

I also added a buildings table. It is referenced in at least two places, so it seems worthy of being its own entity.

This should give you some ideas on how to build your database.