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)

2.users (id=pk)

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

• 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

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)

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

SELECT * FROM checkouts

fiddle

You could create a stored procedure that does the checkout logic for you. It locks the table so that the check for whether the book has been checked out or not followed by the subsequent insert will be treated as a "critical section" only allowing a single call of the procedure to run at a time. Unless the checkout rate is very high, this should not adversely affect performance and ensures that two individuals cannot check out the same book.

DROP PROCEDURE IF EXISTS checkout;

DELIMITER $$
CREATE PROCEDURE checkout(IN checkout_user_id, IN checkout_book_id INT, OUT return_code INT)
NOT DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
    DECLARE row_count INT;

    START TRANSACTION;

    /* Was the book already checked out? */
    SELECT COUNT(*) INTO row_count FROM checkouts
    WHERE book_id = checkout_book_id AND return_date IS NULL
    FOR UPDATE; /* This essentially locks the table */
    
    IF row_count = 0 THEN
        INSERT INTO checkouts(user_id, book_id, checkout_date, return_date)
        VALUES(checkout_user_id, checkout_book_id, CURRENT_DATE(), NULL);
        COMMIT;
        SET return_code = 0;
    ELSE
        /* Book was already checked out. */
        ROLLBACK;
        set return_code = 1;
    END IF;
    
END$$
DELIMITER ;

Having created the stored procedure, you could do:

SET @return_code = 0;
/* user_id 21 wants to checkout book_id 37: */
CALL checkout(21, 37, @return_code);
/* 0 for success otherwise failure: */
SELECT @return_code;