What will happen if a reader who has a book in hand is deleted? How to protect data integrity?

If you delete a reader who has a book checked out, it can lead to inconsistency in the database or data structure: the book is marked as issued, but the reader no longer exists. This results in loss of information about who took the book and violates data integrity.

To protect data integrity, the following approaches can be used:

• Database constraints: set up foreign keys with ON DELETE RESTRICT or ON DELETE CASCADE. For example, prevent deleting a reader if they have issued books.
• Transactions: when deleting a reader, check and update related records (e.g., return books or transfer them to another reader) within a single transaction.
• Application logic: before deletion, check that the reader has no active loans, and either prevent deletion or handle the loans.

Example in Go using a transaction and check:

func DeleteReader(db *sql.DB, readerID int) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    var count int
    err = tx.QueryRow("SELECT COUNT(*) FROM books WHERE reader_id = ?", readerID).Scan(&count)
    if err != nil {
        return err
    }
    if count > 0 {
        return fmt.Errorf("cannot delete a reader with issued books")
    }

    _, err = tx.Exec("DELETE FROM readers WHERE id = ?", readerID)
    if err != nil {
        return err
    }

    return tx.Commit()
}