Help with foreign key code

[proud]

*Mysql version: 5.0.51a

 

I have 3 tables: books, students, and book_loans.

 

(books) contains the fields:          (isbn, title, author, category)

 

(students) contains the fields:      (student_no, name, phone, email)

 

(book_loans) contains the fields:  (isbn, student_no, borrow_date, due_date)

 

 

(isbn) and (student_no) are the primary keys of books and students tables respectively, and both are primary keys in the (book_loans) table.

 

Now, what I want to do is to assign them as foreign keys in the (book_loans) table. When I delete or modify a record in the parent tables, the changes should be applied to the foreign key in the child table (book_loans).

 

Here is the code for the three tables:

 

books table:

 

CREATE TABLE `books` (
`isbn` varchar( 13 ) NOT NULL ,
`title` varchar( 80 ) NOT NULL ,
`author` varchar( 50 ) NOT NULL ,
`category` varchar( 30 ) NOT NULL ,
  PRIMARY KEY ( `isbn` )
) ENGINE = INNODB; 

 

 

students table:

 

CREATE TABLE `students` (
`student_no` varchar( 12 ) NOT NULL ,
`name` varchar( 50 ) NOT NULL ,
`phone` varchar( 20 ) NOT NULL ,
`email` varchar( 50 ) NOT NULL ,
  PRIMARY KEY ( `student_no` )
)  ENGINE = INNODB; 

 

 

book_loans table:

 

  CREATE TABLE `book_loans` (
`isbn` varchar( 13 ) NOT NULL ,
`student_no` varchar( 12 ) NOT NULL ,
`borrow_date` date NOT NULL ,
`due_date` date NOT NULL ,
  PRIMARY KEY ( `isbn` , `student_no` )
) ENGINE = INNODB; 

 

Please add the foreign key code for the book_loans table.

 

 

[fenway]

Please add the foreign key code for the book_loans table.

This sounds like an assignment.