Solved

SQL Table

Posted on 2011-02-18
5
603 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 94
Help in Copy Database Wizard From One SQL Server to Other SQL Server 10 33
updating table data with inner join 9 24
Exchange 2016 Databse move 5 28
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

895 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

11 Experts available now in Live!

Get 1:1 Help Now