SQL Errors

Data:
University.zip

The sql file in that zip isn't updated; use the text at the top of this page.

The sql file was truncated; here's the corrected version:

drop database if exists University;

create database University;

use University;

create table department(

dept_name varchar(20),
building  varchar(15),
budget    numeric(12, 2) check (budget > 0),
constraint dept
    primary key(dept_name)

);

create table instructor(

dept_name varchar(20),
id varchar(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8, 2),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(id)
);

create table course(

course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(course_id)

);

create table classroom(

building varchar(15),
room_number varchar(7),
capacity numeric(4, 0),
primary key(building,room_number),
index(building), #new index
index(room_number) #new index
);


create table section(

course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),

foreign key(course_id) references course(course_id)
    on delete cascade,
foreign key(building) references classroom(building)
    on delete set null,
foreign key(room_number) references classroom(room_number)
    on delete set null,
primary key(course_id,sec_id,semester,year),
#index(building),
#index(room_number),
index(course_id), #new index # doees it have anything to do with the indices?
index(sec_id), #new index
index(semester), #new index
index(year) #new index
);

create table teaches(

id varchar(20),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),


foreign key(course_id) references section(course_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(sec_id) references section(sec_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(semester) references section(semester) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(year) references section(year)
    on delete cascade,
foreign key(id) references instructor(id)
    on delete cascade,
primary key(id,course_id,sec_id,semester,year)
);



create table student(

id varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0),

    foreign key(dept_name) references department(dept_name)
        on delete set null,
index(id) #new index

);

create table advisor(

s_id varchar(5),
i_id varchar(5),

foreign key(i_id) references instructor(id)
    on delete set null,

foreign key(s_id) references student(id)
    on delete cascade,

primary key(s_id)
);

create table takes(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),

    foreign key(course_id) references section(course_id)
        on delete cascade,

    foreign key(sec_id) references section(sec_id)
        on delete cascade,

    foreign key(semester) references section(semester)
        on delete cascade,

    foreign key(year) references section(year) #section(year) -> section(year)
        on delete cascade,

    foreign key(id) references student(id)
        on delete cascade,

    primary key(id,course_id,sec_id,semester,year)

);


create table prereq(

course_id varchar(8),
prereq_id varchar(8),


    foreign key(course_id) references course(course_id)
        on delete cascade,

    foreign key(prereq_id) references course(course_id),

    primary key(course_id,prereq_id)

);

create table timeslot(

time_slot_id varchar(5),
day2 varchar(1),
start_hr int,
start_min int,
end_hr int,
end_min int,

primary key(time_slot_id,day2,start_hr,start_min)	                                 	        	
);

load data infile 'i:/department.txt' into table department fields terminated by ',';
load data infile 'i:/course.txt' into table course fields terminated by ',';
load data infile 'i:/classroom.txt' into table classroom fields terminated by ',';
load data infile 'i:/section.txt' into table section fields terminated by ',';
load data infile 'i:/instructor.txt' into table instructor fields terminated by ',';
load data infile 'i:/teaches.txt' into table teaches fields terminated by ',';
load data infile 'i:/student.txt' into table student fields terminated by ',';
load data infile 'i:/advisor.txt' into table advisor fields terminated by ',';
load data infile 'i:/takes.txt' into table takes fields terminated by ',';
load data infile 'i:/prereq.txt' into table prereq fields terminated by ',';
load data infile 'i:/timeSlot.txt' into table timeSlot fields terminated by ',';

Select allOpen in new window

Ignore the above. This is the proper correction:

drop database if exists University;

create database University;

use University;

create table department(

dept_name varchar(20),
building  varchar(15),
budget    numeric(12, 2) check (budget > 0),
primary key(dept_name),
index(dept_name)
);

create table instructor(

id varchar(20),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8, 2),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(id)
);

create table course(

course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(course_id)

);

create table classroom(

building varchar(15),
room_number varchar(7),
capacity numeric(4, 0),
primary key(building,room_number),
index(building), #new index
index(room_number) #new index
);


create table section(

course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),

foreign key(course_id) references course(course_id)
    on delete cascade,
foreign key(building) references classroom(building)
    on delete set null,
foreign key(room_number) references classroom(room_number)
    on delete set null,
primary key(course_id,sec_id,semester,year),
#index(building),
#index(room_number),
index(course_id), #new index # doees it have anything to do with the indices?
index(sec_id), #new index
index(semester), #new index
index(year) #new index
);

create table teaches(

id varchar(20),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),


foreign key(course_id) references section(course_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(sec_id) references section(sec_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(semester) references section(semester) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(year) references section(year)
    on delete cascade,
foreign key(id) references instructor(id)
    on delete cascade,
primary key(id,course_id,sec_id,semester,year)
);



create table student(

id varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0),

    foreign key(dept_name) references department(dept_name)
        on delete set null,
index(id) #new index

);

create table advisor(

s_id varchar(5),
i_id varchar(5),

foreign key(i_id) references instructor(id)
    on delete set null,

foreign key(s_id) references student(id)
    on delete cascade,

primary key(s_id)
);

create table takes(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),

    foreign key(course_id) references section(course_id)
        on delete cascade,

    foreign key(sec_id) references section(sec_id)
        on delete cascade,

    foreign key(semester) references section(semester)
        on delete cascade,

    foreign key(year) references section(year) #section(year) -> section(year)
        on delete cascade,

    foreign key(id) references student(id)
        on delete cascade,

    primary key(id,course_id,sec_id,semester,year)

);


create table prereq(

course_id varchar(8),
prereq_id varchar(8),


    foreign key(course_id) references course(course_id)
        on delete cascade,

    foreign key(prereq_id) references course(course_id),

    primary key(course_id,prereq_id)

);

create table timeslot(

time_slot_id varchar(5),
day2 varchar(1),
start_hr int,
start_min int,
end_hr int,
end_min int,

primary key(time_slot_id,day2,start_hr,start_min)	                                 	        	
);

load data infile 'i:/department.txt' into table department fields terminated by ',';
load data infile 'i:/course.txt' into table course fields terminated by ',';
load data infile 'i:/classroom.txt' into table classroom fields terminated by ',';
load data infile 'i:/section.txt' into table section fields terminated by ',';
load data infile 'i:/instructor.txt' into table instructor fields terminated by ',';
load data infile 'i:/teaches.txt' into table teaches fields terminated by ',';
load data infile 'i:/student.txt' into table student fields terminated by ',';
load data infile 'i:/advisor.txt' into table advisor fields terminated by ',';
load data infile 'i:/takes.txt' into table takes fields terminated by ',';
load data infile 'i:/prereq.txt' into table prereq fields terminated by ',';
load data infile 'i:/timeSlot.txt' into table timeSlot fields terminated by ',';

Select allOpen in new window

I've read your message. Apparently whether I load the advisor or student table first I still get the error.

If you look at the error it basically says:
Error importing table1. Table2 needs to go before it.

So move table2 above table1.
Run it again. Move again until there are no more errors.

I've done a move. Now I have another problem: I'm getting the error

/* SQL Error (1064): 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 14 */

and I can't make sense of it.

drop database if exists University;

create database University;

use University;


create table department(

dept_name varchar(20),
building  varchar(15),
budget    numeric(12, 2),
check (budget > 0),
primary key(dept_name)

);

create table instructor(

id varchar(20),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8, 2),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(id)
);



create table course(

course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
check (credits > 0),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(course_id)

);

create table classroom(

building varchar(15),
room_number varchar(7),
capacity numeric(4, 0),
primary key(building,room_number),
index(building), #new index
index(room_number) #new index
);


create table section(

course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),

foreign key(course_id) references course(course_id)
    on delete cascade,
foreign key(building) references classroom(building)
    on delete set null,
foreign key(room_number) references classroom(room_number)
    on delete set null,
primary key(course_id,sec_id,semester,year),
#index(building),
#index(room_number),
index(course_id), #new index # doees it have anything to do with the indices?
index(sec_id), #new index
index(semester), #new index
index(year) #new index
);

create table teaches(

id varchar(20),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),


foreign key(course_id) references section(course_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(sec_id) references section(sec_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(semester) references section(semester) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(year) references section(year)
    on delete cascade,
foreign key(id) references instructor(id)
    on delete cascade,
primary key(id,course_id,sec_id,semester,year)
);





create table advisor(

s_id varchar(5),
i_id varchar(5),

foreign key(i_id) references instructor(id)
    on delete set null,

foreign key(s_id) references student(id)
    on delete cascade,

#primary key(s_id),
#index(s_id)
);

create table student(

id varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0),

foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(id),
index(id) #new index

);

create table takes(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),

    foreign key(course_id) references section(course_id)
        on delete cascade,

    foreign key(sec_id) references section(sec_id)
        on delete cascade,

    foreign key(semester) references section(semester)
        on delete cascade,

    foreign key(year) references section(year) #section(year) -> section(year)
        on delete cascade,

    foreign key(id) references student(id)
        on delete cascade,

    primary key(id,course_id,sec_id,semester,year)

);


create table prereq(

course_id varchar(8),
prereq_id varchar(8),


    foreign key(course_id) references course(course_id)
        on delete cascade,

    foreign key(prereq_id) references course(course_id),

    primary key(course_id,prereq_id)

);

create table timeslot(

time_slot_id varchar(5),
day2 varchar(1),
start_hr int,
start_min int,
end_hr int,
end_min int,

primary key(time_slot_id,day2,start_hr,start_min)	                                 	        	
);


load data infile 'i:/course.txt' into table course fields terminated by ',';
load data infile 'i:/department.txt' into table department fields terminated by ',';
load data infile 'i:/classroom.txt' into table classroom fields terminated by ',';
load data infile 'i:/section.txt' into table section fields terminated by ',';
load data infile 'i:/instructor.txt' into table instructor fields terminated by ',';
load data infile 'i:/teaches.txt' into table teaches fields terminated by ',';
load data infile 'i:/advisor.txt' into table advisor fields terminated by ',';
load data infile 'i:/student.txt' into table student fields terminated by ',';

load data infile 'i:/takes.txt' into table takes fields terminated by ',';
load data infile 'i:/prereq.txt' into table prereq fields terminated by ',';
load data infile 'i:/timeSlot.txt' into table timeSlot fields terminated by ',';

Select allOpen in new window

Thank you for your help.