注册

merge插入提示违反唯一性约束

光年 2024/05/11 598 6

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:8.1.2.138
【操作系统】:windows
【CPU】:intel
【问题描述】*:
1、表结构

CREATE TABLE "tb_user_info"
(
"user_id" INT NOT NULL,
"nick_name" VARCHAR(30),
"avatar" VARCHAR(200),
"sex" INT DEFAULT 0 NOT NULL,
"birthday" DATE,
"weight" BIGINT,
"height" BIGINT,
"city" VARCHAR(100),
"address" VARCHAR(200),
"create_time" TIMESTAMP(0) NOT NULL,
"update_time" TIMESTAMP(0) NOT NULL,
NOT CLUSTER PRIMARY KEY("user_id"),
CHECK("sex" >= 0)
,CHECK("height" >= 0)
,CHECK("weight" >= 0))  ;

2、插入数据

INSERT into tb_user_info (user_id, nick_name, avatar, sex, birthday, weight, height, city, address, create_time, update_time)        
VALUES(100017660, NULL, NULL, 0, NULL, 1, 1, NULL, NULL, '2024-05-10 06:37:22', '2024-05-10 06:37:22')

3、执行下列语句提示违反唯一性约束

MERGE INTO tb_user_info a USING (select user_id from tb_user_info) b
ON (a.user_id=100017660 and a.user_id = b.user_id)
WHEN MATCHED THEN UPDATE SET a.update_time=SYSDATE() WHEN NOT MATCHED THEN
INSERT VALUES(100017660, NULL, NULL, 0, NULL, 1, 1, NULL, NULL, '2024-05-10 06:37:22', '2024-05-10 06:37:22');

image.png
4、8.1.2.128、8.1.3.100都不行。

回答 0
暂无回答
扫一扫
联系客服