Hi!!
Yes it works but you need add 2 lines..
At the begin
SET FOREIGN_KEY_CHECKS = 0;
End..
SET FOREIGN_KEY_CHECKS = 1;
---------------------------------------------------------------------------
# Added
SET FOREIGN_KEY_CHECKS = 0;
CREATE DATABASE lab1;
use lab1;
# create student table
# Note: If your version of MySQL complains about the 'ENGINE'
# keyword, substitute 'TYPE'.
CREATE TABLE student (
name VARCHAR(20) NOT NULL,
sex ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
# add records to the student table
INSERT into student (name, sex, student_id) values
('derek','M',NULL),
('sandy','F',NULL),
('shayne','M',NULL),
('summer','F',NULL),
('pat','F',NULL)
;
# create grade event table
CREATE TABLE grade_event (
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
# add records to the grade_event table
INSERT into grade_event (date, category, event_id) values
('06/02/05','T',NULL),
('06/02/05','Q',NULL),
('06/07/05','Q',NULL),
('06/08/05','T',NULL),
('06/08/05','Q',NULL)
;
# create score table
# The PRIMARY KEY is composed of 2 columns to prevent andy combination
# of event_id/student_id from appearing more than once.
CREATE TABLE score (
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
PRIMARY KEY (event_id, student_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
# add records to the score table
INSERT into score (student_id, event_id, score) values
('2','3','98'),
('5','4','80'),
('6','1','90'),
('2','4','78'),
('3','2','85')
;
# create absent table
CREATE TABLE absent (
student_id INT UNSIGNED NOT NULL,
date DATE NOT NULL,
reason VARCHAR(20) NOT NULL,
absent_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (absent_id, student_id),
INDEX (student_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
# add records to the absent table
INSERT into absent (student_id, date, reason, absent_id) values
('2','05/20/05','sick',NULL),
('4','05/08/05','vacation',NULL),
('3','05/23/05','sick',NULL),
('5','06/02/05','personal',NULL)
;
# create picture table
CREATE TABLE picture (
student_id INT UNSIGNED NOT NULL,
date_mod DATE NOT NULL,
picture_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (picture_id),
INDEX (student_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
# add records to the picture table
INSERT into picture (student_id, date_mod, picture_id) values
('3','05/12/05',NULL),
('3','05/23/05',NULL),
('2','06/01/05',NULL),
('5','05/04/05',NULL);
# Added
SET FOREIGN_KEY_CHECKS = 1;
---------------------------------------------------------------------------
I saved this file as ushastry.sql & moved to /mysql/bin directory..
Below I have dumped your file..
C:\mysql\bin>mysql -uroot -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31 to server version: 4.0.18-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
(root@localhost) [(none)]>
(root@localhost) [(none)]> SOURCE ushastry.sql
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.11 sec)
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.13 sec)
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.14 sec)
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.09 sec)
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 2
Query OK, 0 rows affected (0.09 sec)
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 1
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [lab1]>
Also you need to change the date format it should be YYYY-MM-DD..
Regards,
Umesh Shastry
http://www.blogger.com/profile/02551756983528645221