如何在Postgres中禁用PK变化？

发布于 2025-01-29 12:49:53 字数 155 浏览 4 评论 0原文

如何禁用DB的PK更改？是否可以？

例如，我有一个表：

user
id name email password ...
1  Alex ...   ...
2  Mark ...   ...

如何禁用更改用户的可能性。

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

顾冷 2025-02-05 12:49:53

您可以使用数据库参考机制。创建一个虚拟表，该表引用您要保护的列，并将访问权限撤销到该表的其他用户。示例：

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);

参考的默认模式是限制删除或更新：

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".

另外，您可以定义触发器以放弃不需要的修改，例如

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

在 db＆lt;＆gt; fiddle。

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.

回复收藏 0 原文

~没有更多了~