Geek Answers Handbook

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

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

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

I think this has something to do with the rules in the delete statement, but I could not find anything remotely close to my problem in the documentation.

+5
source share
5 answers

BEFORE DELETE trigger . ( PK, "boolean" ), RAISE .

PostgreSQL, , :

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();
+9

, . - , . , :

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;

: . , , , .

+9

( ), . FK .

+1

, . , delete. , , , , , .

?

: " , , . . . , , , , , , , , . , , , ".

. .
http://www.postgresql.org/docs/8.3/interactive/rules-triggers.html

0

More articles:


All Articles