Connect students and books with the borrow table

Now we get to the most important part of the database. The borrow table records the relationship between a student and a book.

Important: Create this table after students and books. The foreign keys below refer to those tables.

Add:

code sql

CREATE TABLE IF NOT EXISTS borrow (
    borrow_id INT AUTO_INCREMENT PRIMARY KEY,

    student_id INT NOT NULL,
    book_id INT NOT NULL,

    borrow_date TIMESTAMP NOT NULL
        DEFAULT CURRENT_TIMESTAMP,

    borrow_return_date TIMESTAMP NULL
        DEFAULT NULL,

    CONSTRAINT fk_borrow_student
        FOREIGN KEY (student_id)
        REFERENCES students(student_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT fk_borrow_book
        FOREIGN KEY (book_id)
        REFERENCES books(book_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_general_ci;

How the relationships work

The first foreign key connects a borrow record to a student:

code text

students.student_id
        │
        ▼
borrow.student_id

The second foreign key connects a borrow record to a book:

code text

books.book_id
        │
        ▼
borrow.book_id

This prevents the borrow table from referring to students or books that do not exist.

What about `ON DELETE RESTRICT`?

It prevents a student or book from being deleted while it is still referenced by a borrowing record. That helps protect the borrowing history.

format_list_numbered Step 7 of 19

chevron_left Previous Next chevron_right

Connect students and books with the borrow table

How the relationships work

What about ON DELETE RESTRICT?

What about `ON DELETE RESTRICT`?