ORA-00001: unique constraint violated

From: sanar <sanarx_at_hotmail.com>
Date: 14 Nov 2004 22:16:09 -0800
Message-ID: <bc632bde.0411142216.483e8bec@posting.google.com>

Hi All, I have a question regarding the "ORA-00001: unique constraint violated" message. Here is the situation.

I have a table TABLE-A that is cleared by an application at the start of every year (don't ask why just how it was written I guess).

The TABLE-A currently contains 1468 rows.

I have imported another version of TABLE-A before it was truncated by the application from 1 year back.

This table TABLE-B has 9815 rows and is the same structure as TABLE-A.

Table A has the following constraints which are relevant.

CONSTRAINT_NAME		CONSTRAINT_TYPE 	TABLE_NAME	COLUMN_NAME	
G_ATTACHMENT_ASSOC_PK 	P			TABLE_A		ATTACHMENT_ASSOC_ID
G_ATTACHMENT_ASSOC_FK1	R			TABLE_A		ATTACHMENT_ID
G_ATTACHMENT_ASSOC_FK2 	R			TABLE_A		MODULE_TYPE_ID


SQL> desc TABLE_A
 Name                            Null?    Type
 ------------------------------- -------- ----
 U_VERSION                                CHAR(1)
 ATTACHMENT_ASSOC_ID             NOT NULL NUMBER(15)
 MODULE_ID                       NOT NULL NUMBER(15)
 MODULE_TYPE_ID                  NOT NULL NUMBER(15)
 ATTACHMENT_ID                   NOT NULL NUMBER(15)

SQL> desc TABLE_B
 Name                            Null?    Type
 ------------------------------- -------- ----
 U_VERSION                                CHAR(1)
 ATTACHMENT_ASSOC_ID             NOT NULL NUMBER(15)
 MODULE_ID                       NOT NULL NUMBER(15)
 MODULE_TYPE_ID                  NOT NULL NUMBER(15)
 ATTACHMENT_ID                   NOT NULL NUMBER(15)

I know that ATTACHMENT_ASSOC_ID is the primary key of table A and table B. I also know that no key in table B exists in table A. I can use the following query to check this.  

SQLPLUS> SELECT COUNT(*) FROM USER.TABLE_A A WHERE A.ATTACHMENT_ASSOC_ID IN
(SELECT DISTINCT B.ATTACHMENT_ASSOC_ID FROM USER.TABLE_B B)
/

COUNT(*)

---

        0

What I want to do is insert all the rows from table B into table A
(append to table A). When I try to do this with the following
statement I get the following error.

SQLPLUS > INSERT INTO USER.TABLE_A SELECT * FROM USER.TABLE_B INSERT INTO USER.TABLE_A SELECT * FROM USER.TABLE_B

                     *

ERROR at line 1:
ORA-00001: unique constraint (USER.G_ATTACHMENT_ASSOC_I1) violated

I check the G_ATTACHMENT_ASSOC_I1 constraint – it doesn't exist. An index exists which looks like the following.

SQL> select * from dba_indexes where index_name = 'G_ATTACHMENT_ASSOC_I1'; OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES
USER G_ATTACHMENT_ASSOC_I1 USER TABLE_A TABLE UNIQUE I am not sure how to proceed – should I drop this index or disable it and then insert using the select statement? None of the constraints on TABLE A should be violated by the insert. I have checked the foreign key references and ensured the primary key is not duplicated.

We are running oracle 7.3.4 on Unix. I'm sorry if this is a stupid question but I am new to this and wasn't able to find enough info after searching for ages.

Any help would be appreciated.

Thanks

Jay Received on Mon Nov 15 2004 - 00:16:09 CST