1

Is there code to not allow a value directly after a value that's already saved be inserted into the table. The field is an ENUM.

Cant find code anywhere

None available for ENUM

Timeslot ENUM('09:00','09:30','10:00') 09:00 saved already

'09:30' shouldn't be allowed to be inserted into the table '10:00' should insert fine

Gary Mannion
  • 29
  • 7

1 Answers1

0

There is no default function that I am aware of to do what you are wanting.

I think you will have to do some checks using the ENUM index. ENUM values are mapped to a numeric index. You can select column_name+0 on an ENUM column and that will give you the index value of the ENUM rather than the ENUM value. MySQL ENUM Doc

In your case the ENUM index would look something like this:

NULL -> NULL
0 -> ''
1 -> '9:00'
2 -> '9:30'
3 -> '10:00'

For instance, if you have 1 record with Timeslot set to '9:00' and you 'SELECT TimeSlot+0 FROM table' your result for the record will be 1. If the column value was '9:30' the index would be 2, etc.

You can find the potential index of an incoming value using something like this:

    SELECT FIND_IN_SET('new_value', REPLACE(SUBSTRING(column_type,6, LENGTH(column_type) - 6), '\'', '') ) AS enum_options
      FROM information_schema.columns
     WHERE column_name='your_enum_column'
       AND table_schema = 'your_schema';

If the result of this is equal to any of the index values (or index value +1) of any of the values already in the table, you do not want to allow this new entry. You can use the above query as a subquery inside a case statement to compare this new value's index to your previous values' indexes.

EDIT (4/2/2019): After a couple of comments I think that the following may get you closer to what you need. I have not been able to test this query out, but it should be close.

       CREATE TEMPORARY TABLE booking_conflicts AS (
            SELECT MAX(
                       IF(
                          FIND_IN_SET( 
                           (SELECT FIND_IN_SET('12:00', REPLACE(SUBSTRING(column_type,6, LENGTH(column_type) - 6), '\'', '') )
                              FROM information_schema.columns
                             WHERE column_name='your_enum_column'
                               AND table_name = 'booking'
                               AND table_schema = 'your_schema'),
                            CONCAT(time_slot+0, ',', time_slot+1)
                           ) > 0,
                           1,
                           0) AS is_time_conflict
              FROM booking
             WHERE facility_id = 6
               AND booking_date = '2020-07-04'
       );

       INSERT INTO bookings 
                   (facility_id,booking_date,time_slot,member_id) 
       VALUES (6,'2020-07-04','12:00',2)
        WHERE (SELECT is_time_conflict FROM booking_conflicts) = 0;

What this is doing is getting all used time_slots from that date for that facility and comparing them with the new time slot you are trying to use. If the new time slot's index is equal to the index of a previously used time_slot or of a previously used time_slot + 1, then the query will return 1, otherwise 0. We store that in a temp table and access the temp table from the insert.

derek.wolfe
  • 1,086
  • 6
  • 11
  • Thanks for the response. I am just having a little trouble how i would fit this into a case statement – Gary Mannion Apr 02 '19 at 12:10
  • Insert into bookings (facility_id,booking_date,time_slot,member_id) Values (6,'2020-07-04','12:00',2) WHERE SELECT FIND_IN_SET('new_value', REPLACE(SUBSTRING(column_type,6, LENGTH(column_type) - 6), '\'', '') ) AS enum_options FROM information_schema.columns WHERE column_name='time_slot' AND table_schema = 'mylescoop' What i have tried already, not sure how to link the where clause – Gary Mannion Apr 02 '19 at 12:53
  • I'm adding a new SQL snippet to the answer above that may be helpful. – derek.wolfe Apr 02 '19 at 17:04
  • Thanks for the response, very clear and understandable, could this be integrated into a trigger. So that the temporary table would store a value where the trigger is activated on before an insert into bookings. Thus running an error message off of it then. As this would allow for a more streamlined database as it would automatically check this every time an insert occurs on the table – Gary Mannion Apr 02 '19 at 20:24
  • I don't see why it couldn't be. Though, you may want to consider creating a stored procedure/function instead. This way you could easily have the function/procedure return whether or not the new record was created, so you can use that value to notify the user on success/fail. – derek.wolfe Apr 03 '19 at 12:58
  • Thanks that makes logical sense. What would be the layout to that procedure/function as i am used of triggers but not used to stored to procedures/functions. – Gary Mannion Apr 03 '19 at 13:57
  • It will depend on your specific use case. A good explanation of the differences in stored procedures and functions can be found here: https://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when Docs for the create syntax: https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html – derek.wolfe Apr 03 '19 at 14:54
  • I have tried to fix it myself, but cant seem to find the error, its is giving me a syntax error when i try to run it. The error is a syntax error after As is_time_conflict at line 12. Just asking that maybe you could spot an error as i think i have been looking at it for too long!. I am using this in the MySQL mamp enviroment also Thanks in advance – Gary Mannion Apr 06 '19 at 13:48