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 want to define a BEFORE DELETE trigger on the table. When you attempt to delete the row (either match by PK or have a separate "protect" boolean column), RAISE an exception.

I'm not familiar with PostgreSQL syntax, but it looks like this is how you'd do it:

CREATE FUNCTION check_del_cat() RETURNS trigger AS $check_del_cat$
    BEGIN            
        IF OLD.ID = 1 /*substitute primary key value for your row*/ THEN
            RAISE EXCEPTION 'cannot delete default category';
        END IF;

    END;
$check_del_cat$ LANGUAGE plpgsql;

CREATE TRIGGER check_del_cat BEFORE DELETE ON categories /*table name*/
    FOR EACH ROW EXECUTE PROCEDURE check_del_cat();

Answer 2

The best way I see to accomplish this is by creating a delete trigger on this table. Basically, you'll have to write a stored procedure to make sure that this 'default' category will always exist, and then enforce it using a trigger ON DELETE event on this table. A good way to do this is create a per-row trigger that will guarantee that on DELETE events the 'default' category row will never be deleted.

Please check out PostgreSQL's documentation about triggers and stored procedures:

http://www.postgresql.org/docs/8.3/interactive/trigger-definition.html

http://www.postgresql.org/docs/8.3/interactive/plpgsql.html

There's also valuable examples in this wiki:

http://wiki.postgresql.org/wiki/A_Brief_Real-world_Trigger_Example

Answer 3

You could have a row in another table (called defaults) referencing the default category. The FK constraint would not let the deletion of the default category happen.

Answer 4

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.

Answer 5

Keep in mind how triggers work. They will fire off for every row your delete statement will delete. This doesn't mean you shouldn't use triggers just keep this in mind and most importantly test your usage scenarios and make sure performance meets the requirements.

Should I use a rule or a trigger?

From the official docs: "For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must execute its operations many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right."

See the docs for details.
http://www.postgresql.org/docs/8.3/interactive/rules-triggers.html