Search code examples
mysqldatabasebooleancolumnsorting

Can't have consecutive rows with the same value with bit datatype in MySQL

I'm doing a calendar program and for my database I'm using MySQL.

I have a table called 'evento' (event) in which one of the columns is a Boolean called 'Tarea' (Pending). This column is used to determine if the event is marked a pending or not.

Image of the table

I researched and the data type for Booleans in MySQL appears to be bit. The problem that Im having is that every time I input elements into the table, the bit variable has to be 0 and 1 but in a consecutive order meaning I cant have two or more rows that consecutively have the value 0 or 1.

Image of the table and the bit column

When I try to modify the value in the bit column and it doesn't match the consecutive 1 and 0 like for example:

UPDATE `calendario`.`evento` SET `Tarea` = '0' WHERE (`idevento` = '6'); 
//when the row with id = 5 or 7 has a value of 0 as well`

MySQL props me with the error:

Operation failed: There was an error while applying the SQL script to the database.
Executing:
UPDATE `calendario`.`evento` SET `Tarea` = '0' WHERE (`idevento` = '6');

ERROR 1406: 1406: Data too long for column 'Tarea' at row 1
SQL Statement:
UPDATE `calendario`.`evento` SET `Tarea` = '0' WHERE (`idevento` = '6')

Is there any way to solve this issue or am I maybe using the wrong datatype? Help.

Solution

  • '0' isn't zero, it's the character that represents zero. Lose the quotes and you should be OK:

    UPDATE `calendario`.`evento` SET `Tarea` = 0 WHERE (`idevento` = '6'); 
-- Here -----------------------------------^