I have a Postges DB. I have a table of Categories and I want 1 and only 1 to be considerd as "Default". It seems I could have a boolean field with a unique constraint and just use null in lieu of False values, and that would work but I would prefer a setup where if I mark a row as default, it will automatically set that to null on all other rows. I would also like to ensure there is one set as default. I could create a second table with 1 row and 1 column to track the id of the default row, that I could enforce to must have a value and to be a valid id, but that seems overkill to have a table just for that. Is there an easier way?
You have multiple options.
You can create a stored procedure (or 3 stored procedures) in PostgreSQL to insert, update and delete data. In the stored procedure, you can add the logic to ensure that at least one of the rows is set to default and rest of them to not default.
You can create a trigger, which after insert update or delete applies the same logic.
I like the idea of having a separate table called categories_default. This allows you a bit more flexibility and less of an overkill, in my opinion, than the method above. If you type in that table that category_id 123 is default, but tomorrow your requirement changes such that you want a default by category_id + color combination, you can do that with a table like this. It's also easy to update rather than the options above. In the options above, if you want to mark a single row as default, you have to either scan through all rows, find the existing default, switch that to not default and then apply your selected row as default. So, there's a lot of reads/writes.
Your idea with the extra table is a good one Just make sure to have a foreign key constraint from that one-row table to your table. Ensure that the table cannot contain more than one row with an index:
CREATE UNIQUE INDEX ON single ((1));