oracle ORA-00001: 违反了唯一约束主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26745508/
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
ORA-00001: unique constraint primary key violated
提问by Akarsh Kolanu
DELETE from dbo.T_LIAV_AGENT_STATE_APPROVAL SAP
WHERE EXISTS (SELECT UNIQUE 1 FROM MCS_SYNC_STATE_APPR APP
inner join MCS_SYNC_NAME SN on SN.SE_NAME_ID = APP.SE_NAME_ID
and SN.ACTION in('U','S')
Where APP.SE_name_id = SAP.AV_NAME_ID
and APP.SYNC_INSTANCE_ID = param_inst_ID);
COMMIT;
INSERT INTO dbo.T_LIAV_AGENT_STATE_APPROVAL
SELECT UNIQUE
APP.SE_NAME_ID AS AV_NAME_ID,
APP.STATE AS AV_STATE,
APP.APPROVAL_TYPE AS AV_APPROVAL_TYPE,
APP.START_DATE AS AV_START_DATE,
APP.END_DATE AS AV_END_DATE,
APP.APPOINTED AS AV_APPOINTED,
APP.RENEWAL_DATE AS AV_RENEWAL_DATE,
APP.LICENSE AS AV_LICENSE,
COMPANY_NAME_ID as AV_COMPANY_CODE,
SYSDATE AS TSTAMP,
SYNC_USER_NAME_ID AS AV_FIRST_USER_ID,
SYSDATE AS AV_FIRST_DATE,
NULL AS AV_LAST_USER_ID,
NULL AS AV_LAST_DATE
FROM MCS_SYNC_STATE_APPR APP
WHERE exists (select 1 from t_liag_agent AG
where AG.ag_name_id = APP.SE_NAME_ID)
and APP.SYNC_INSTANCE_ID = param_inst_ID;
Here is the SQL part and I am getting error when I debug the code:
这是 SQL 部分，调试代码时出现错误：
ORA-00001: unique constraint primary key violated At T_LIAV_AGENT_STATE_APPROVAL...
ORA-00001: 在 T_LIAV_AGENT_STATE_APPROVAL 处违反了唯一约束主键...
and these are the 2 columns which the primary key constraint relies on AV_NAME_ID, AV_STATE..
这些是主键约束依赖的 2 列 AV_NAME_ID, AV_STATE..
There is no duplicate data as per me ...Do you think any other reason?
根据我的说法没有重复的数据......你认为还有其他原因吗？
回答by Dmitriy
If you have 'CREATE TABLE' privilege, use dbms_errlog package.
如果您有“CREATE TABLE”权限，请使用 dbms_errlog 包。
Run script:
begin dbms_errlog.create_error_log('DBO.T_LIAV_AGENT_STATE_APPROVAL'); end; /Run your INSERT script with an additional clause:
INSERT INTO dbo.T_LIAV_AGENT_STATE_APPROVAL SELECT ... FROM ... LOG ERRORS INTO err$_T_LIAV_AGENT_STATE_APPROVAL REJECT LIMIT UNLIMITED;Check error logging table for errors:
select * from err$_T_LIAV_AGENT_STATE_APPROVAL;
运行脚本：
begin dbms_errlog.create_error_log('DBO.T_LIAV_AGENT_STATE_APPROVAL'); end; /使用附加子句运行 INSERT 脚本：
INSERT INTO dbo.T_LIAV_AGENT_STATE_APPROVAL SELECT ... FROM ... LOG ERRORS INTO err$_T_LIAV_AGENT_STATE_APPROVAL REJECT LIMIT UNLIMITED;检查错误日志表中的错误：
select * from err$_T_LIAV_AGENT_STATE_APPROVAL;
You will see all records, that violated constraints, and error messages.
您将看到所有违反约束的记录和错误消息。
回答by Akarsh Kolanu
Select statement was missing a column when compared to Insert table statement so there was this error. When i added that column to the select statement it worked perfectly.
与 Insert table 语句相比，Select 语句缺少一列，因此出现此错误。当我将该列添加到 select 语句时，它运行良好。
回答by Shreya Srivastava
If you are not sure which unique constraint was violated, you can run the following SQL:
如果您不确定违反了哪个唯一约束，可以运行以下 SQL：
Select Distinct table_name
From all_indexes
Where index_name = 'CONSTRAINT_NAME';
