Unique constraint on SQL insert -ORACLE

Question

Im running a script which would fire a select query and insert the results into a table.

The select query is

select distinct a.child child_id, a.parent parent_id from cat a, par b WHERE a.child=b.catentid and b.catenttype_id='Product' and a.reltype_id='PRODUCT_ITEM'

and inserted into the table which is created as

create table TI_CAT_0 ( child_id NUMBER not null,parent_id NUMBER not null,PRIMARY KEY (child_id))

But I get a unique key constraint violation while running the script as "SYS_C00187123", and I checked this constraint name in all_constraints table and its on the TI_CAT_0 table only.

Since I use the distinct command, I'm not sure why this violation is turning up. Its a Oracle DB.

are you sure the child_id you're selecting doesn't already exist in TI_CAT_0 table — CodeBird
distinct operates on all columns of the select, not just the first one. So you can have the same value for child_id with different values for parent_id and it would still be distinct. — a_horse_with_no_name
@a_horse_with_no_name So any way we can have the distinct child id? — user3199077

woemler woemler · Accepted Answer · 2014-03-11T01:16:46

Assuming you are creating the TI_CAT_0 table from scratch and inserting records from your SELECT query, then you are either unintentionally inserting the same record more than once, or your initial query is returning multiple rows for each child_id. If this is the case, you should run this query to see if your initial query is returning duplicate child_id values. Your query, as it is written, will return unique combinations of child_id and parent_id. You can check to see if multiple parents are associated with a single child with the following SQL:

select 
    a.child, 
    count(a.parent) as parent_count
from 
    cat a
    join par b 
        on a.child = b.catentid 
where 
    b.catenttype_id='Product' 
    and a.reltype_id='PRODUCT_ITEM'
group by 
    a.child
having
    count(a.parent) > 1
order by 2 desc

The results (if any) will be all child_id values associated with multiple parent_id values.

Unique constraint on SQL insert -ORACLE

1 Answers