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

Solution

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