JCW2
asked on
University SQL Load
In my code, I'm trying to understand how I can get data extracted from the tables. Can you tell me what I'm doing wrong? (This file is intended to be unzipped and then have its contents applied)
University.zip
University.zip
ASKER
Yes.
Did you check the URL?
ASKER
I just did. I noticed I forgot to provide the load statements:
load data infile 'i:/advisor.txt' into table advisor fields terminated by ',';
load data infile 'i:/classroom.txt'into table classroom fields terminated by ',';
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:/instructor.txt' into table instructor fields terminated by ',';
load data infile 'i:/prereq.txt' into table prereq fields terminated by ',';
load data infile 'i:/section.txt' into table section 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:/teaches.txt' into table teaches fields terminated by ',';
load data infile 'i:/timeSlot.txt' into table timeSlot fields terminated by ',';
and got the following errors:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`advisor`, CONSTRAINT `sid` FOREIGN KEY (`s_id`) REFERENCES `
student` (`id`) ON DELETE CASCADE)
Query OK, 5 rows affected (0.12 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`course`, CONSTRAINT `dept3` FOREIGN KEY (`dept_name`) REFERE
NCES `department` (`dept_name`) ON DELETE SET NULL)
Query OK, 7 rows affected (0.05 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1406 (22001): Data too long for column 'id' at row 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`prereq`, CONSTRAINT `courid` FOREIGN KEY (`course_id`) REFER
ENCES `course` (`course_id`) ON DELETE CASCADE)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`section`, CONSTRAINT `coursecon` FOREIGN KEY (`course_id`) R
EFERENCES `course` (`course_id`) ON DELETE CASCADE)
Query OK, 13 rows affected (0.06 sec)
Records: 13 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`takes`, CONSTRAINT `cours` FOREIGN KEY (`course_id`) REFEREN
CES `section` (`course_id`) ON DELETE CASCADE)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`teaches`, CONSTRAINT `identity` FOREIGN KEY (`id`) REFERENCE
S `instructor` (`id`) ON DELETE CASCADE)
Query OK, 20 rows affected (0.03 sec)
Records: 20 Deleted: 0 Skipped: 0 Warnings: 0
What do you know about this?
load data infile 'i:/advisor.txt' into table advisor fields terminated by ',';
load data infile 'i:/classroom.txt'into table classroom fields terminated by ',';
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:/instructor.txt' into table instructor fields terminated by ',';
load data infile 'i:/prereq.txt' into table prereq fields terminated by ',';
load data infile 'i:/section.txt' into table section 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:/teaches.txt' into table teaches fields terminated by ',';
load data infile 'i:/timeSlot.txt' into table timeSlot fields terminated by ',';
and got the following errors:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`advisor`, CONSTRAINT `sid` FOREIGN KEY (`s_id`) REFERENCES `
student` (`id`) ON DELETE CASCADE)
Query OK, 5 rows affected (0.12 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`course`, CONSTRAINT `dept3` FOREIGN KEY (`dept_name`) REFERE
NCES `department` (`dept_name`) ON DELETE SET NULL)
Query OK, 7 rows affected (0.05 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1406 (22001): Data too long for column 'id' at row 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`prereq`, CONSTRAINT `courid` FOREIGN KEY (`course_id`) REFER
ENCES `course` (`course_id`) ON DELETE CASCADE)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`section`, CONSTRAINT `coursecon` FOREIGN KEY (`course_id`) R
EFERENCES `course` (`course_id`) ON DELETE CASCADE)
Query OK, 13 rows affected (0.06 sec)
Records: 13 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`takes`, CONSTRAINT `cours` FOREIGN KEY (`course_id`) REFEREN
CES `section` (`course_id`) ON DELETE CASCADE)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`teaches`, CONSTRAINT `identity` FOREIGN KEY (`id`) REFERENCE
S `instructor` (`id`) ON DELETE CASCADE)
Query OK, 20 rows affected (0.03 sec)
Records: 20 Deleted: 0 Skipped: 0 Warnings: 0
What do you know about this?
You have to do the imports in the right order because of foreign key constraints. In English: you can't add a row to the advisors table for a student tha doesn't yet exist in the students table.
ASKER
One issue I'm having: separating the values for salary and ID.
ASKER
I've resolved that with commas; however, I'm still getting an error saying "SQL Error (1406): Data too long for column 'id' at row 1" for the instructor.txt and instructor table.
ASKER
Update:
ERROR 1406 (22001): Data too long for column 'id' at row 1
Query OK, 13 rows affected (0.04 sec)
Records: 13 Deleted: 0 Skipped: 0 Warnings: 0
Query OK, 5 rows affected (0.18 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1062 (23000): Duplicate entry '1-Spring-2010' for key 'PRIMARY'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`teaches`, CONSTRAINT `identity` FOREIGN KEY (`id`) REFERENCE
S `instructor` (`id`) ON DELETE CASCADE)
Query OK, 13 rows affected (0.07 sec)
Records: 13 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`takes`, CONSTRAINT `cours` FOREIGN KEY (`course_id`) REFEREN
CES `section` (`course_id`) ON DELETE CASCADE)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`advisor`, CONSTRAINT `iid02` FOREIGN KEY (`i_id`) REFERENCES
`instructor` (`id`) ON DELETE SET NULL)
Query OK, 20 rows affected (0.03 sec)
Records: 20 Deleted: 0 Skipped: 0 Warnings: 0
I'm still confused.
ERROR 1406 (22001): Data too long for column 'id' at row 1
Query OK, 13 rows affected (0.04 sec)
Records: 13 Deleted: 0 Skipped: 0 Warnings: 0
Query OK, 5 rows affected (0.18 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1062 (23000): Duplicate entry '1-Spring-2010' for key 'PRIMARY'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`teaches`, CONSTRAINT `identity` FOREIGN KEY (`id`) REFERENCE
S `instructor` (`id`) ON DELETE CASCADE)
Query OK, 13 rows affected (0.07 sec)
Records: 13 Deleted: 0 Skipped: 0 Warnings: 0
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`takes`, CONSTRAINT `cours` FOREIGN KEY (`course_id`) REFEREN
CES `section` (`course_id`) ON DELETE CASCADE)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`advisor`, CONSTRAINT `iid02` FOREIGN KEY (`i_id`) REFERENCES
`instructor` (`id`) ON DELETE SET NULL)
Query OK, 20 rows affected (0.03 sec)
Records: 20 Deleted: 0 Skipped: 0 Warnings: 0
I'm still confused.
University.sql
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,
salary numeric(8, 2),
constraint dept2
foreign key(dept_name) references department(dept_name)
on delete set null,
constraint id1
primary key(id)
);
create table course(
course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
constraint dept3
foreign key(dept_name) references department(dept_name)
on delete set null,
constraint cid1
primary key(course_id)
);
create table classroom(
building varchar(15),
room_number varchar(7),
capacity numeric(4, 0),
constraint build
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),
constraint coursecon
foreign key(course_id) references course(course_id)
on delete cascade,
constraint struct
foreign key(building) references classroom(building)
on delete set null,
constraint room
foreign key(room_number) references classroom(room_number)
on delete set null,
constraint sid1
primary key(sec_id,semester,year),
index(course_id), #new index
index(sec_id), #new index
index(semester), #new index
index(year) #new index
);
create table teaches(
id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
constraint course
foreign key(course_id) references section(course_id) #department(sec_id) -> section(sec_id)
on delete cascade,
constraint section
foreign key(sec_id) references section(sec_id) #department(sec_id) -> section(sec_id)
on delete cascade,
constraint sem
foreign key(semester) references section(semester) #department(sec_id) -> section(sec_id)
on delete cascade,
constraint year1
foreign key(year) references section(year)
on delete cascade,
constraint identity
foreign key(id) references instructor(id)
on delete cascade,
constraint multid
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),
constraint dept4
foreign key(dept_name) references department(dept_name)
on delete set null,
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),
constraint cours
foreign key(course_id) references section(course_id)
on delete cascade,
constraint sec
foreign key(sec_id) references section(sec_id)
on delete cascade,
constraint sem02 #sem -> sem02
foreign key(semester) references section(semester)
on delete cascade,
constraint time
foreign key(year) references section(year) #section(year) -> section(year)
on delete cascade,
constraint iid
foreign key(id) references student(id)
on delete cascade,
constraint multid2
primary key(id,course_id,sec_id,semester,year)
);
create table advisor(
s_id varchar(5),
i_id varchar(5),
constraint iid02 #iid -> iid02
foreign key(i_id) references instructor(id)
on delete set null,
constraint sid
foreign key(s_id) references student(id)
on delete cascade,
constraint sid2
primary key(s_id)
);
create table prereq(
course_id varchar(8),
prereq_id varchar(8),
constraint courid
foreign key(course_id) references course(course_id)
on delete cascade,
constraint prereq2
foreign key(prereq_id) references course(course_id),
constraint ids2
primary key(course_id,prereq_id)
);
create table timeslot(
time_slot_id varchar(5),
day varchar(1),
start_hr int,
start_min int,
end_hr int,
end_min int,
constraint time
primary key(time_slot_id,day,start_hr,start_min)
);
load data infile 'i:/department.txt' into table department fields terminated by ',';
load data infile 'i:/instructor.txt' into table instructor 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:/teaches.txt' into table teaches 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:/advisor.txt' into table advisor 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 ',';
instructor.txt
10101,Srinivasan,Comp. Sci.,65000,
12121,Wu,Finance,90000,
15151,Mozart,Music,40000,
22222,Einstein,Physics,95000,
32343,El Said,History,60000,
33456,Gold,Physics,87000,
45565,Katz,Comp. Sci.,75000,
58583,Califieri,History,62000,
76543,Singh,Finance,80000,
76766,Crick,Biology,72000,
83821,Brandt,Comp. Sci.,92000,
98345,Kim,Elec. Eng.,80000
Whatever is in instructor.txt has something too long for the id column. Or you can make the id column bigger.
ASKER
I've increased the size of the offending item; now I'm getting:
"SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (`university`.`instructor` , CONSTRAINT `dept2` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL)"
Can you explain what that means?
"SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (`university`.`instructor`
Can you explain what that means?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
as we figure out the constraints of the database, you should be rearranging your load statements to load the files in the correct order.
ASKER
I took a look, but I couldn't figure out what was missing in the load statement order at this point. Department.txt is loaded before Instructor.txt, and "Instructor" appears to depend on "Department".
ASKER
My next error after this is: Duplicate entry '1-Spring-2010' for key 'PRIMARY'. However, I couldn't find that data in the text files.
so the column with the data '1-spring-2010' is being used as a primary key (pk). A pk must be unique. So either you have two rows in the text file with that same data in that column, or you are not deleting the database or clearing out the tables inbetween imports. Your .sql file starts off with "drop database if exists University;" so I assume that one of the import files has duplicate information. Or you are just running the import part of the script and not the whole thing.
ASKER
What about the problem before that?
(I wrote:
I took a look, but I couldn't figure out what was missing in the load statement order at this point. Department.txt is loaded before Instructor.txt, and "Instructor" appears to depend on "Department".)
(I wrote:
I took a look, but I couldn't figure out what was missing in the load statement order at this point. Department.txt is loaded before Instructor.txt, and "Instructor" appears to depend on "Department".)
ASKER
I've just noticed "1,Spring,2010" is being interpreted as "1-spring-2010". How does that happen?
ASKER
The issue from last message is resolved.
ASKER
The issue from the last message is resolved.
ASKER
I'm sorry; I've missed one of your comments
"translation:
you can't add the row to the instructor table before his/her department exists in the department table"
"translation:
you can't add the row to the instructor table before his/her department exists in the department table"
ASKER
I don't understand what's causing the error.
Update:
Update:
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 ',';
/* 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) */
ASKER
Thank you for your help.
http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html