Questions › How to find the record that violate unique key constraint?
Channa Asked

Assume I have two tables as follows,

Table A

    +-------+-------+-------+-------+
    | col_a | col_b | col_c | col_d |
    +-------+-------+-------+-------+
    |       |       |       |       |
    +-------+-------+-------+-------+

Table B

    +-------+-------+-------+-------+
    | col_a | col_b | col_c | col_d |
    +-------+-------+-------+-------+
    |       |       |       |       |
    +-------+-------+-------+-------+

I'm going to update Table A using Table B. Here are the conditions

  1. records that are equal by col_a should update in Table A
  2. records are not equal by col_a should inserted to Table A
  3. Table A has a unique key constraint as (col_b,col_c,col_d)

Problem is when updating data in Table A, this unique key constraint fails for some records. Question is how can I identify records that violate unique key constraint using a query. (I don't have access to logs)

Comments :
Paparazzi replied

Would help if you posted the query that is failing

AdamMc331 replied

Well you can search for records that have the (col_b, col_c, col_d) group that you're inserting, right? That would tell you the record that already exists.

Are you using MySQL or Oracle?

Channa replied

@Mark ,I'm using Oracle.

Channa replied

@Frisbee, it is part of a long stored procedure, therefore difficult to post it here.


4 Answers :
Glenn answered

If you don't have a unique key on col_b, col_c, col_d of table_b, this will result in a violation when copying over. You can identify problematic rows with a query like this:

SELECT col_b, col_c, col_d
  FROM table_b
  GROUP BY col_b, col_c, col_d
  HAVING COUNT(*) > 1

A similar query can be run on table_a joined to table_b, but the specific queries to run will depend on which columns will be updated in table_a. For the insert case, a useful technique might be to use a MINUS between table_a and the proposed inserted row.

Ellesedil replied
In the event that the a set of values for b, c, and d both exist in tables A and B, but those records have different values for column a, would it make sense to include table A into your count > 1 query? Otherwise, there could be a record in table B that is unique, but still violate the constraint when the OP goes to insert it.;
Paparazzi replied
The stated problem is update not copy;
Glenn replied
@Ellesedil: yes, but to make the proper query we need to know which columns in table_a are to be updated. That is why I left it at a "similar" query. @Frisbee: one part of the question was inserting from one table to another. I consider that copying from one table to another. That was one part that could be partially addressed without further info.;
Aleksej answered

If I correctly understand your need, maybe something like this can find the rows that will give problems:

select *
from table_a a
inner join table_b b 
on (a.col_b  = b.col_b and
    a.col_c  = b.col_c and
    a.col_d  = b.col_d and
    a.col_a != b.col_a
   )
milind brahme answered

USE Merge Statement Update when matched on Cols b,c,d and Insert when not matched.

A single Merge statement would take care of your problem Ex.

            MERGE INTO A a
            USING B b
            ON (a.colb = b.colb and a.colc=b.colc and so on..)
            WHEN MATCHED THEN
            UPDATE SET a.cola= b.cola
            WHEN NOT MATCHED THEN
            INSERT (collist)
            VALUES (b.cols);
Glenn replied
On the insert side: if table "b" has duplicates, inserting them both into "a" would throw an exception. On the update side: if it matches on "a" but modifies "b", "c", or "d" in such a way as to introduce a duplicate in "a", this would also not work. So this merge probably will not help the op identify the duplicates.;
milind brahme replied
He can use log_error into clause and process failures later , just a thought.;
Hawk answered

I just identified the violator of unique constraint with the following steps (which I think are pretty straightforward):

  1. Drop the constraint/unique index on Table A
  2. Run your query to update/insert/merge into Table A
  3. Run

    select col_b, col_c, col_d, count(*)
    from Table A
    group by col_b, col_c, col_d
    having count(*) > 1
    
  4. The query results show your culprit(s)
  5. Fix data/query
  6. Recreate the constraint/unique index on Table A
  7. Rerun query and it should work (fingers crossed)