ORA-00001 Unique constraint (string.string) violated

Question

I have two tables that are identical to each other t1 and t2, but t2 has more data than t1. I'm using this query to insert the missing data from t2 to t1.

insert into t1
select * from t2
where not exist ( select * from t1
                  where t1.key1 = t2.key1
                  and t1.key2 = t2.key2)

When this query is run I get the: ORA-00001 Unique constraint (string.string) violated error.

The two tables have key1 and key2 as keys.

Since the only constraint is the two keys I don't understand why I'm getting that error.

EDIT: I noticed now in "Indexes" that there are 2 constraints both are of type unique.

The first one is: key1, random_column The second one is: key2

sorry for the inconvenience.

One of the keys it hits error. I've tried to only use one of the where statements which the error msg popsup on. But I get the same error message. — President Camacho

Marlin Pierce Marlin Pierce · Accepted Answer · 2013-03-14T15:18:38

Just in case there is a different understanding about the unique constraint, I am assuming that the unique constraint is one unique index on both fields. If you have a unique constraint on key1 and a unique constraint on key2, then this will fail when there is a record in t1 with the same t2.key1 value, but a different t2.key2 value, because adding the record would result in two records in t1 with the same key1 value, which is forbidden by a unique constraint on key1.

If this is what you have, you need a unique index with both fields, rather than column constraints.

One possibility is that a value in t2 has a NULL key1 or a NULL key2.

In an expression, a NULL input always results in a NULL result which is considered false.

So, if t2 has a record with NULL key1 and a value of 'value2' for key2, then the where clause is evaluating

select * from t1
where t1.key1 = NULL and t1.key2 = 'value2'

This is not equivalent to

select * from t1
where t1.key1 is NULL and t1.key2 = 'value2'

instead t1.key1 = NULL will be untrue, the select will fail to ever return a result, the exist will be false and NOT(exist) will be true. But if t1 already has a record like this, the unique constraint will fail.

So use this insert statement.

insert into t1
select * from t2
where not exist ( select * from t1
                  where (t1.key1 = t2.key1 or (t1.key1 is null and t2.key1 is null))
                  and (t1.key2 = t2.key2 or (t1.key2 is null and t2.key2 is null)))

ORA-00001 Unique constraint (string.string) violated

2 Answers