How do I force one record to have a true value for a boolean column, and all others a false value?

I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.

Basically, I want to guarantee that this query will always return exactly one row:

SELECT ID, Zip 
FROM PostalCodes 
WHERE isDefault=True

How would I do that in SQL?

Note: This answer was given before it was clear the asker wanted something MySQL specific. This answer is biased towards SQL Server.

Apply a CHECK constraint to the table that calls a UDF and makes sure its return value is

• As gbn pointed out, UDFs may return inconsistent results if you are using snapshot isolation in SQL Server. However, an inline UDF does not run into this issue, and since a UDF that just does a count is inline-able, this is not an issue here.

• The strength of a database engine's processing power is in its ability to work on sets of data at once. With a UDF-backed check constraint, the engine will be limited to applying this UDF serially to every row that is modified. In your use case it is unlikely that you will perform mass updates to the PostalCodes table, however this remains a performance concern for those situations where set-based activity is likely.

Given all these caveats, I recommend using gbn's suggestion of a filtered unique index instead of a check constraint.

StackExchange Database Administrators Q#4794, answer score: 10