Could I make a column in a table only allows one 'true' value and all other rows should be 'false'

Rather than having the boolean attribute in the table, you could have another table that contains one row and points to the row in the original table that you consider true.

Changing the true value is a matter of updating the foreign key in the TrueRow table.


In MySQL, a unique index will ignore any NULL values. So there's a bit of a hack you could use.

You could consider adding a nullable bit column (which can only have the value 1 or 0):

ALTER TABLE mytable 
  ADD COLUMN `is_default` BIT NULL,
  ADD UNIQUE INDEX `is_default_UNIQUE` (`is_default` ASC);

At this point, one row can be 1 (TRUE), and attempting to add another row that is also TRUE, will result in an error:

1062: Duplicate entry '\x01' for key 'is_default_UNIQUE'

The only catch is that all other rows need to be NULL and cannot be false as that would also count as a unique value.

Example table data

(This really isn't what indexes are meant to be used for though)

Tags:

Mysql