How do I disable PK changes in my db? Is it possible?
For example, I have this table:
user
id name email password ...
1 Alex ... ...
2 Mark ... ...
How to disable the possibility of changing user.id?
CodePudding user response:
You can use the database reference mechanism. Create a dummy table that references the column you want to protect and revoke the access privilege to this table for other users. Example:
create table users(
id int primary key,
name text);
insert into users values
(1, 'John');
create table users_restrict(
id int references users);
insert into users_restrict
values (1);
The default mode of the reference is to restrict deletes or updates:
update users set
id = 2
where id = 1;
ERROR: update or delete on table "users" violates foreign key constraint "users_restrict_id_fkey" on table "users_restrict"
DETAIL: Key (id)=(1) is still referenced from table "users_restrict".
Alternatively, you can define a trigger to abandon modifications you do not want, e.g.
create or replace function protect_users_id()
returns trigger language plpgsql as $$
begin
if old.id <> new.id then
raise exception 'Cannot change users.id';
end if;
end $$;
create trigger protect_users_id
before update on users
for each row execute procedure protect_users_id();
Test it in db<>fiddle.