Solved

SQL Errors

Posted on 2011-02-22
11
329 Views
Last Modified: 2012-05-11
I need to understand the error in the following:
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 ',';

/* SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (`university`.`advisor`, CONSTRAINT `advisor_ibfk_2` FOREIGN KEY (`s_id`) REFERENCES `student` (`id`) ON DELETE CASCADE) */

Open in new window

0
Comment
Question by:JCW2
11 Comments
 
LVL 38

Accepted Solution

by:
Aaron Tomosky earned 167 total points
ID: 34958218
Same problem as your last question, you are importing the files in an order that does not follow the freeing key constraints. I.e. You can't create a teacher in a department if the department doesn't exist yet. You can't create a class if the teacher doesn't exist yet...
I can't explain it any clearer. Maybe someone else can.
0
 
LVL 14

Assisted Solution

by:Scott Madeira
Scott Madeira earned 167 total points
ID: 34960698
To narrow it down,  create your database tables first.

Then execute lines 164 to 174 one at a time and see where it blows up.  that will tell you where the problem is.

You didn't provide all of the sql definition and data so there is no way for us to help you find the exact problem.
0
 

Author Comment

by:JCW2
ID: 34961145
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:JCW2
ID: 34961163
The sql file in that zip isn't updated; use the text at the top of this page.
0
 

Author Comment

by:JCW2
ID: 34962024
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 ',';

Open in new window

0
 

Author Comment

by:JCW2
ID: 34962078
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 ',';

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 166 total points
ID: 34962450
JCW2,

  the explanation of the error has been given: you must
  either:
  * load the tables in the order so that the "parent tables" are loaded first
  OR
  * create the tables without the foreign keys, then load data in any order, then create the foreign keys
   
  the second option will only work for a initial load

  please clarify if you understood that?
0
 

Author Comment

by:JCW2
ID: 34962690
I've read your message. Apparently whether I load the advisor or student table first I still get the error.
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 34962862
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.
0
 

Author Comment

by:JCW2
ID: 34964410
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 ',';

Open in new window

0
 

Author Closing Comment

by:JCW2
ID: 34965340
Thank you for your help.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Exchange 2010 Database wont mount 3 46
Exchange 2013 free and busy between mailbox databases 77 98
AWS EC2 & RDS Instance 5 35
What's wrong with this T-SQL Foreign Key? 7 46
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question