Question 189036 in Mysql Question posted in
The official MySQL documentation can be found here.

a library db , the goal is to prevent more than one user trying to check out the same book at the same time Mysql workbench 8.0

im a beginner and using mysql workbench8.0 ,and it seems like i cant get this to work. ive got three tables.

these are my tables:

1.books(id=pk)

id title author
1 my first book sara
2 my second book maman
3 my third book baba

2.users (id=pk)

id full_name last_login
1 ala 1973-07-03
2 badi 1972-07-03
3 saly 2015-10-18

3.checkouts (id=pk, user_id=fk , book_id=fk)

id user_id book_id checkout_date return_date
1 1 1 2000-10-18 2017-10-18
2 1 2 2015-11-18 null
3 2 3 2015-10-28 2025-10-18
4 3 2 2016-10-18 it should give me an error

  • user_id=fk is the child table of userstable.

  • book_id=fk is the child table of the books table.

My solution is : when there is a duplicate for the book_id column, there cant be duplicates for the return_date column.

so when inserting row number 4, its not supposed to let me ; because user (3) cant checkout sth that doesnt have a return_date.

ive tried unique index for those two columns(book_id,return_date ) so that i can have unique combos , but since MySQL allows duplicates for NULL values, i didnt get anywhere with that.

and i also still dont know php or python.

what are the other solutions that ill be able to try?

Your help would mean a lot

and sorry for my english

Tags:

2

Answers


  1. 0

    you can use a trigger to prevent that

    for the trigger you need eventually to add DELIMITER, depend on gui and method

    In bulk insert will 1 error prevent the insertion of all rows ind that session

    CREATE TABLE books
    (`id` int, `title` varchar(14), `author` varchar(5))
;
    
INSERT INTO books
    (`id`, `title`, `author`)
VALUES
    (1, 'my first book', 'sara'),
    (2, 'my second book', 'maman'),
    (3, 'my third book', 'baba')
;


    CREATE TABLE users
    (`id` int, `full_name` varchar(4), `last_login` varchar(10))
;
    
INSERT INTO users
    (`id`, `full_name`, `last_login`)
VALUES
    (1, 'ala', '1973-07-03'),
    (2, 'badi', '1972-07-03'),
    (3, 'saly', '2015-10-18')
;


    CREATE TABLE checkouts
    (`id` int, `user_id` int, `book_id` int, `checkout_date` Date, `return_date` date
  )
;
    


    CREATE TRIGGER before_wcheckouts_insert
BEFORE INSERT
ON checkouts FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(100);
    IF EXISTS( SELECT 1 FROM checkouts 
  WHERE  `book_id` = NEW.`book_id` AND (`return_date` > NEW.`checkout_date` OR `return_date` IS NULL))   THEN
        set msg = "INSERTTriggerError: Trying to checkout book, that isn't returned.";
        signal sqlstate '45000' set message_text = msg;
    END IF; 

END 

    INSERT INTO checkouts
    (`id`, `user_id`, `book_id`, `checkout_date`, `return_date`)
VALUES
    (1, 1, 1, '2000-10-18', '2017-10-18'),
    (2, 1, 2, '2015-11-18', NULL),
    (3, 2, 3, '2015-10-28', '2025-10-18')
;

    INSERT INTO checkouts
    (`id`, `user_id`, `book_id`, `checkout_date`, `return_date`)
VALUES

  (4,   3 , 2   , '2016-10-18', NULL)

    INSERTTriggerError: Trying to checkout book, that isn't returned.

    SELECT * FROM checkouts
    id user_id book_id checkout_date return_date
    1 1 1 2000-10-18 2017-10-18
    2 1 2 2015-11-18 null
    3 2 3 2015-10-28 2025-10-18

    fiddle

    Login or Signup to reply.
  2. 0

    I believe that instead of trying to prevent the row insertion showing that a book is being checked out, you need to update the existing row that shows that the book has not been checked in, which must be in error. Let me explain:

    It seems to me that in a "real world" situation, a borrower checks out a book by presenting it to the librarian for checkout, right? The fact that the book is in someone’s hand means that the book has either never been checked out or that it was checked out and physically returned. It is possible in the second case that the librarian forgot to do whatever was necessary to update the database and that is why the return_date for this checkout is still NULL. So instead of not checking out the book, I think in this case you should first replace the NULL value in the return_date column for book_id = 2 with the current date showing that it has now been checked in. Then you can proceed to check out the book.

    So I would execute the following two statements:

    /* Check in the book if we did not check it in when it was physically returned. */
UPDATE checkouts SET return_date = CURRENT_DATE()
WHERE book_id = 2 AND return_date is NULL;

/* Now check out the book. */
INSERT INTO checkouts(user_id, book_id, checkout_date, return_date)
VALUES(3, 2, CURRENT_DATE(), NULL);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search