oracle 如何修复 SQL 错误：ORA-00001：违反唯一约束 (ALERTS2.PK_UP_MODULE_MASTER)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28982279/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I fix SQL Error: ORA-00001: unique constraint (ALERTS2.PK_UP_MODULE_MASTER) violated
提问by kittu
Gives me error when I try to insert and it finally inserts after couple of retries with out any error.
当我尝试插入时给我错误，它最终在几次重试后插入而没有任何错误。
SQL Error: ORA-00001: unique constraint (ALERTS2.PK_UP_MODULE_MASTER) violated.
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
INSERT statement:
插入语句：
INSERT INTO up_module_master
(
mr_id,
mr_name,
mr_desc,
mr_parent,
created_by,
created_date,
modified_date,
module_type,
module_link,
link_text,
help_text shortcut_link,
shortcut_name,
shortcut_flag,
module_display_name
)
SELECT max(u1.mr_id)+1,
'Notification Engine',
'Notification Engine Module',
0,1,
SYSDATE,
'',
'',
'../../jalsweb/Logout.jsp',
'HTTP',
'',
'',
'',
0,
'Notification Engine'
FROM up_module_master u1;
Below is the table definition:
下面是表定义：
CREATE TABLE "up_module_master"
(
"mr_id" NUMBER (10, 0) NOT NULL ENABLE,
"mr_name" VARCHAR2 (200 byte) NOT NULL ENABLE,
"mr_desc" VARCHAR2 (250 byte),
"mr_parent" NUMBER,
"created_by" NUMBER,
"created_date" TIMESTAMP (6),
"modified_date" TIMESTAMP (6),
"module_type" VARCHAR2 (100 byte),
"module_link" VARCHAR2 (200 byte),
"link_text" VARCHAR2 (250 byte),
"help_text" VARCHAR2 (250 byte),
"shortcut_link" VARCHAR2 (400 byte),
"shortcut_name" VARCHAR2 (100 byte),
"shortcut_flag" NUMBER,
"module_display_name" VARCHAR2 (100 byte),
"audit_type" VARCHAR2 (100 byte),
"service_id" NUMBER,
"module_regis_type" NUMBER DEFAULT 1,
CONSTRAINT "PK_UP_MODULE_MASTER" PRIMARY KEY ("mr_id"),
CONSTRAINT "UP_MODULE_MASTER_UP_MODUL_FK1" FOREIGN KEY ("mr_parent")
REFERENCES "up_module_master" ("mr_id") ENABLE
)
回答by twoleggedhorse
Looks like MR_IDis not an identity seed. If another process is attempting to insert a record at the same time as you, both will try to insert the same ID and you will get this error. I suggest that you change the column to be auto incrementing. You will need to create a sequence and then a trigger if using pre version 12 or the identity column if using later versions:
看起来MR_ID不是身份种子。如果另一个进程与您同时尝试插入记录，则两者都将尝试插入相同的 ID，您将收到此错误。我建议您将列更改为自动递增。如果使用 12 之前的版本，您将需要创建一个序列，然后创建一个触发器，如果使用更高版本，则需要创建标识列：
Oracle: how to create an identity column?
The link above has all the details for doing this.
上面的链接包含执行此操作的所有详细信息。
回答by Pavel Gatnar
the problem is, that someone other inserted and commited a row with the same key meanwhile consider using a sequence (it looks like the sequence is already defined): SELECT seq_name.nextval, ... FROM dual
问题是，其他人插入并提交了具有相同键的行，同时考虑使用序列（看起来序列已经定义）： SELECT seq_name.nextval, ... FROM dual
回答by Boneist
The reason why it's failing is because "select max(mr_id) + 1 ..." is going to be a fixed number for all rows that you're selecting - it's not going to increment for each row in your select statement.(Duh! See comments below!)
它失败的原因是因为“select max(mr_id) + 1 ...”将是您选择的所有行的固定数字 - 它不会为您的 select 语句中的每一行增加。（废话！见下面的评论！）
As others have said, use a sequence! (Yes, there are other ways you could use to get unique values as part of your select statement, but they'd be the wrong thing to use. Sequences FTW!)
正如其他人所说，使用序列！（是的，您可以使用其他方法来获取唯一值作为选择语句的一部分，但使用它们是错误的。序列 FTW！）
