人大金仓 KingBase 表添加自增主键 duplicate key value violates unique constraint

查询表拥有的自增主键

SELECT column_name, column_default
FROM information_schema.columns
WHERE table_name = 'sys_deploy_form' 
AND column_default LIKE 'nextval%';

创建自增序列

CREATE SEQUENCE "PUBLIC"."sys_deploy_form_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

将自增序列给表

ALTER SEQUENCE "PUBLIC"."sys_deploy_form_seq" OWNED BY "PUBLIC"."sys_deploy_form"."id";

将表主键给自增序列生成下一个主键

ALTER TABLE ONLY "PUBLIC"."sys_deploy_form" ALTER COLUMN "id" SET DEFAULT NEXTVAL('PUBLIC.sys_deploy_form_seq'::REGCLASS);

给用户赋予权限

ALTER TABLE "PUBLIC"."sys_deploy_form_seq" OWNER TO "SYSTEM";

当表中有数据，将序列设置从当前主键开始

duplicate key value violates unique constraint

SELECT setval('sys_role_role_id_seq', (SELECT max(role_id) FROM sys_role));