oracle 没有重复但仍然违反约束错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6044818/
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
no duplicates but still constraint violation error
提问by user682571
I have table in which a constraint has been set on a field called LoginId.While inserting a new row i am getting an error on this constratint associated with this field(LoginID)stating the below error.
我有一个表，其中在名为 LoginId 的字段上设置了约束。在插入新行时，我在与此字段 (LoginID) 相关联的此约束上出现错误，说明以下错误。
The insert command is below: Type 1 with sequence
插入命令如下：键入 1 和序列
insert into TemplateModule
(LoginID,MTtype, Startdate TypeId, TypeCase, MsgType, MsgLog, FileName,UserName, CrID, RegionaltypeId)
values
(MODS_SEQ.NEXTVAL,3434,2843,2453,2392,435,2390,'pension.txt','rereee',454545,3434);
Failed with error
Type 2 without sequence a hardcoded value::
键入 2 无序列硬编码值::
insert into TemplateModule
(LoginID,MTtype, Startdate TypeId, TypeCase, MsgType, MsgLog, FileName,UserName, CrID, RegionaltypeId)
values
(3453,3434,2843,2453,2392,435,2390,'pension.txt','rereee',454545,3434)
I crosschecked many times for duplicates.But nothing found.What could be the rootcause
我反复检查了很多次。但没有找到。什么可能是根本原因
ORA-00001: unique constraint error (LGN_INDEX)violated
回答by Jordan Parmer
First, do a describe on LGN_INDEX on that table to make absolutely certain you are looking at the right column. Is LGN_INDEX a constraint+index or just an index? Try re-building your index to make sure it isn't corrupt. Make sure you don't have any other constraints that might be interfering.
首先，对该表上的 LGN_INDEX 进行描述，以绝对确定您正在查看正确的列。LGN_INDEX 是约束+索引还是只是索引？尝试重新构建索引以确保它没有损坏。确保您没有任何其他可能干扰的约束。
Second, perform a SELECT MAX(LOGINID) FROM TEMPLATEMODULEand compare that to the next sequence value to make sure your sequence isn't set lower than the maximum ID you are working with.
其次，执行 aSELECT MAX(LOGINID) FROM TEMPLATEMODULE并将其与下一个序列值进行比较，以确保您的序列设置不低于您正在使用的最大 ID。
Third, check if you have any triggers on that table.
第三，检查该表上是否有任何触发器。
If none of these things work, try re-creating the table using just the schema. Cross-load the data and try again. There might be a configuration setting on that table that is causing the issue. CREATE TABLE MY_TEMP AS SELECT * FROM TEMPLATEMODULE.
如果这些都不起作用，请尝试仅使用架构重新创建表。交叉加载数据并重试。该表上可能存在导致问题的配置设置。 CREATE TABLE MY_TEMP AS SELECT * FROM TEMPLATEMODULE.
回答by user2093748
I encountered the same problem.
我遇到了同样的问题。
An Insert statement populating an Integer value (not in the table) to the Primary Key column.
将整数值（不在表中）填充到主键列的插入语句。
The problem was a before trigger tied to a sequence. The next_val for the sequence was already present in the table.
问题是与序列相关的前触发器。序列的 next_val 已经存在于表中。
The trigger fires, grabs the sequence number and fails with a Primary Key violation.
触发器触发，获取序列号并因主键违规而失败。
回答by unnamedPlayer
I encountered this same issue while importing from an excel file. I thought the file was free of duplicates until I tried removing duplicates in excel.
我在从 excel 文件导入时遇到了同样的问题。在我尝试在 excel 中删除重复项之前，我认为该文件没有重复项。
To find and remove duplicates in excel,
要在excel中查找和删除重复项，
- Select the data.
Ctrl + ashould work. - Click
Data -> Remove Duplicates - Select the fields that have the constraints in your database and click OK
- Excel should remove any duplicate records based on the fields selected at step 3 above.
- 选择数据。
Ctrl + a应该管用。 - 点击
Data -> Remove Duplicates - 选择数据库中具有约束的字段，然后单击“确定”
- Excel 应根据在上述步骤 3 中选择的字段删除任何重复记录。
You should now be able to import records from the file into your db.
您现在应该能够将文件中的记录导入到您的数据库中。
