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.
(This really isn't what indexes are meant to be used for though)