Solved

SQL Table

Posted on 2011-02-18
5
596 Views
Last Modified: 2012-05-11
I'm trying to find how I messed up a foreign key constraint. Can you tell me what's wrong?
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,
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,
primary key(course_id)

);

create table classroom(

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


create table section(

course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year2 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,
primary key(sec_id,semester,year2)
);

create table teaches(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year2 numeric(4,0),

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

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

constraint sem
    foreign key(semester) references department(semester)
        on delete cascade,

constraint year1
    foreign key(year2) references section(year2)
        on delete cascade,

constraint identity
    foreign key(id) references instructor(id)
        on delete cascade,
primary key(id,course_id,sec_id,semester,year2)
);


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


);

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 sem
    foreign key(semester) references section(semester)
        on delete cascade,
constraint time
    foreign key(year) references section(year)
        on delete cascade,
constraint iid
    foreign key(id) references student(id)
        on delete cascade,
primary key(id,course_id,sec_id,semester,year)
);

create table advisor(

s_id varchar(5),
i_id varchar(5),
constraint iid
    foreign key(i_id) references instructor(id)
        on delete set null,
constraint sid
    foreign key(s_id) references student(id)
        on delete cascade,
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),

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,
primary key(time_slot_id,day,start_hr,start_min)	                                 	        	
);

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 ';';

Open in new window

0
Comment
Question by:JCW2
  • 2
  • 2
5 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 34931745
Rather than just dumping a whole heap of code and then leaving us to guess it would be polite to say what you have an issue with? What is your problem? What errors do you get? Symptoms of the problem?
0
 
LVL 1

Expert Comment

by:stezeb
ID: 34932684
Well, before asking you to be more specific, are you familiar with MySQL storage engines?

http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html

If you're using MyISAM engine (default prior to MySQL 5.5.5), foreign key constraints doesn't have any real effect.

For full foreign key constraints support, you need to switch to InnoDB engine. You can do that by putting the following line into the beginning of your script.

SET storage_engine=InnoDB;

Open in new window


Now, if this doesn't cover your question, could you please be more specific?
0
 

Author Comment

by:JCW2
ID: 34935139
1 - I have the newest version.

2 - I'm getting the error: SQL Error (1005): Can't create table 'university.section' (errno: 150)
                                      Foreign key constraint is incorrectly formed
0
 
LVL 1

Accepted Solution

by:
stezeb earned 500 total points
ID: 34936906
Oh yes, I see where is the problem now.

Foreign keys must refer to rows, which are indexed. Usually FKs are refered to rows with primary key constraint, which are indexed automatically. If you want to refer them to 'plain' columns, you need to set those to be indexed.

For details, see this page:
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

I tried to run your script, after adding necessary indexes, and there was few more errors:
1) In FKs for table teaches, i belive you wanted to refer to table section instead of deparment
2) In table takes, i belive you wanted to refer to year2 instead of year
3) And finally, there was duplicate constraint names in tables takes and advisor

Here is the corrected script with commented changes:
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,
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,
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),
year2 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,
primary key(sec_id,semester,year2),
index(course_id), #new index
index(sec_id), #new index
index(semester), #new index
index(year2) #new index
);

create table teaches(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year2 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(year2) references section(year2)
        on delete cascade,

constraint identity
    foreign key(id) references instructor(id)
        on delete cascade,
primary key(id,course_id,sec_id,semester,year2)
);


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(year2) #section(year) -> section(year2)
        on delete cascade,
constraint iid
    foreign key(id) references student(id)
        on delete cascade,
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,
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),

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,
primary key(time_slot_id,day,start_hr,start_min)	                                 	        	
);

Open in new window

0
 

Author Closing Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now