CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperunique-key
divya.trehan573
divya.trehan573

Reputation: 564

Unique key constraint error while inserting in SQL

There are two table x_ass_table and i_ass_table :

I am inserting data from x_ass_table with 13000 records to i_ass_table:

There is a unique constraint in i_ass_table

 CREATE UNIQUE INDEX i_ass_table_pk ON i_ass_table(ASSIGNMENT_NUMBER,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,EFFECTIVE_LATEST_CHANGE)

insert into i_ass_table select * from x_ass_table;

I am getting a unique constraint error 

00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

This has to be an error with the data. But how do i check which data is courrupt or duplicate out of 13000 records

Upvotes: 2

Views: 2016

Answers (3)

Raghvendra
Raghvendra

Reputation: 134

As you asked, you will have to check in both tables for the unique constraint conflict.

  1. First check in x_ass_table if there is any duplicate record based in unique key, that you can check with query provided by Mureinik on above answer.

  2. Second, you will have to check if there is same record present in both table based on unique key that can also create unique constraint conflict. This you can check with below query. 

    select *  
  from x_ass_table x,  
       i_ass_table i  
where i.assignment_number = x.assignment_number  
  and i.effective_start_date = x.effective_start_date  
  and i.effective_end_date = x.effective_end_date  
  and i.effective_latest_change = x.effective_latest_change;

Upvotes: 2

K.Gomu
K.Gomu

Reputation: 11

You can try

SELECT * FROM x_ass_table GROUP BY ASSIGNMENT_NUMBER HAVING COUNT(*)>1

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

You could number the source rows by combination of the fields you want unique using the analytic version of the count() function and then query to see which combination has more than one row. E.g.:

SELECT *
FROM   (SELECT *,
               COUNT() OVER (PARTITION BY assignment_number, 
                                          effective_start_date,
                                          effective_end_date,
                                          effective_latest_change) AS c
        FROM   x_ass_table) t
WHERE  c > 1

Upvotes: 2

Related Questions