sql - How to prevent deletion of the first row in table (PostgreSQL)?

Question

Is it possible to prevent deletion of the first row in table on PostgreSQL side?

I have a category table and I want to prevent deletion of default category as it could break the application. Of course I could easily do it in application code, but it would be a lot better to do it in database.

I think it has something to do with rules on delete statement, but I couldn't find anything remotely close to my problem in documentation.

Answer 1

You were right about thinking of the rules system. Here is a link to an example matching your problem. It's even simpler than the triggers:

create rule protect_first_entry_update as
  on update to your_table
  where old.id = your_id
  do instead nothing;
create rule protect_first_entry_delete as
  on delete to your_table
  where old.id = your_id
  do instead nothing;

Some answers miss one point: also the updating of the protected row has to be restricted. Otherwise one can first update the protected row such that it no longer fulfills the forbidden delete criterion, and then one can delete the updated row as it is no longer protected.