0

I am trying to create a small sample database for customer appointments and need to have a constraint on what can be inputted regarding the appointment time in relation to how many people already have an appointment in a certain 25 min block. So my goal is that only 4 people can have an appointment between XX:00 and XX:25 (e.g. 10:00 - 10:25) and if another customer tries to make an appointment in that time block they cannot do so and an error is returned. Thus I know the code needs to first consider the date of the appointment, then look at the time attempting to be entered and check in the database if there are already 4 appointments in that time block and reject the input or if there is fewer than 4 people and allow the input

I do not have much experience in MYSQL and usually code in bash or python so I keep thinking of variables and if statements. I have tried to do nested select loops and have looked into CHECK constraints but have not managed to work it out. Below is the CREATE TABLE statement and my attempt at making a nested SELECT loop.

    --customer_number INT (20) AUTO_INCREMENT PRIMARY KEY,
    desired_appointment_date DATE NOT NULL,
    desired_appointment_time TIME NOT NULL,
    reason_for_visit VARCHAR (200) NOT NULL,
    --FOREIGN KEY (customer_number) REFERENCES customers (customer_number) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO appt_info(desired_appointment_date,desired_appointment_time, reason_for_visit) 
    SELECT '2022-10-25','11:30:00','Meeting'
        FROM appt_info
        WHERE (desired_appointment_time BETWEEN )
Jesse Hix
  • 83
  • 5
  • 1
    Constraint cannot provide this restriction. Use BEFORE INSERT trigger. – Akina Oct 19 '22 at 17:11
  • Does your condition means the next - for each row in the table the difference between `time` and `LEAD(time, 4)` must be above 25 min.? Or you have some pre-defined 25-minute quantums and the amount of appointments must be checked within the quantum only? – Akina Oct 19 '22 at 17:14

0 Answers0