Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-00001: unique constraint violated

Re: ORA-00001: unique constraint violated

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Mon, 15 Nov 2004 20:41:24 -0800
Message-ID: <1100580003.26332@yasure>


sanar wrote:
> 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

SELECT trigger_name
FROM all_triggers
WHERE table_name = <table_name>;

And a better check for dups would be:

SELECT <column>
FROM table_a
INTERSECT
SELECT <column>
FROM table_b;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Mon Nov 15 2004 - 22:41:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US