CODEWITHSUNDEEP
sqlh2
user5182503
user5182503

Reputation:

How to disable delete on table for certain id in h2?

Lets suppose we have student table in h2 and every student has id (primary key). Is it possible to implement trigger (or another mechanism) to disable delete operation if id == 100. I want to have such protection on DB level, but not on application level.

Upvotes: 0

Views: 234

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

One solution is obviously a trigger that prevents deleting or changing the value.

Another method is to use a foreign key constraint. Create a table of ids that you want to keep and use a foreign key reference:

create table keep_these_students (
    student_id int,
    constraint fk_keep_these_students_student_id foreign key (student_id) references students(id)
);

insert into keep_these_students (student_id)
    values (100);

The foreign key definition will require that the row cannot be deleted if the id changes. And, it is easy to add additional ids -- without changing triggers.

Upvotes: 1

Related Questions