Solved

SQL Error 150

Posted on 2011-02-23
10
1,496 Views
Last Modified: 2012-05-11
The code here has the error:

/* SQL Error (1005): Can't create table 'university.advisor' (errno: 150)  Foreign key constraint is incorrectly formed */

but I can't understand how to address it. I tried to use "index" and this didn't seem to be relevant. Can you tell me what I'm missing?
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),
index(dept_name),
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)
#index(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 'j:/department.txt' into table department fields terminated by ',';
load data infile 'j:/course.txt' into table course fields terminated by ',';
load data infile 'j:/classroom.txt' into table classroom fields terminated by ',';
load data infile 'j:/section.txt' into table section fields terminated by ',';
load data infile 'j:/instructor.txt' into table instructor fields terminated by ',';
load data infile 'j:/teaches.txt' into table teaches fields terminated by ',';
load data infile 'j:/student.txt' into table student fields terminated by ',';
load data infile 'j:/advisor.txt' into table advisor fields terminated by ',';
load data infile 'j:/takes.txt' into table takes fields terminated by ',';
load data infile 'j:/prereq.txt' into table prereq fields terminated by ',';
load data infile 'j:/timeSlot.txt' into table timeSlot fields terminated by ',';

Open in new window

University.zip
0
Comment
Question by:JCW2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 416 total points
ID: 34966753
You're trying to create a FK on student, but student hasn't been created yet. Move the advisors create below the student create.
0
 

Author Comment

by:JCW2
ID: 34966952
Okay, now I'm getting

/* 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) */

How do I interpret and deal with that?
0
 
LVL 79

Assisted Solution

by:arnold
arnold earned 84 total points
ID: 34967032
You have to adjust the loading of data to match the constrains that you have.
As derekkromm pointed out with the create directive, you need to load the advisor table data prior to the student one.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 416 total points
ID: 34967044
Right - put the loads in the same order as your creates to ensure you aren't violating your own FKs
0
 

Author Comment

by:JCW2
ID: 34967071
Placing the load statement for "advisor" before "student" doesn't seem to do anything.

(It looked like Derekkromm was talking about moving the create statements, above the load statements.)
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 416 total points
ID: 34967089
I was, but all of your loads should be in the same order as your creates since that is the logical progression of the tables.

All of the table creates themselves work if you remove the loads, right?
0
 

Author Comment

by:JCW2
ID: 34967111
I have the orders the same now. I am however getting the same error:

/* 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) */
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 416 total points
ID: 34967192
you must have some data in your advisor file that contains student ids that don't exist in the student table
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 416 total points
ID: 34967202
if you want to test, i'd suggest removing that FK, inserting the data, and then running a query similar to:

select * from advisor where s_id not in (select s_id from student) to get a list of student ids that are in advisor but not student
0
 

Author Closing Comment

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

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This video teaches viewers about errors in exception handling.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

626 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