Originally posted by kanthbethi
And Abhay i tried not exists before I posted it here, so which is obvious that it wouldn't work.
I wonder how did it work for me...and still it do, it only depends on how you may/will structure your query.

PS

Code:
TEST:ABHAY> Create Table Test_PK(ID1 number, ID2 number, ID3 Number, ID4 Number, ID5 NUmber, ID6 Number,
  2  ID7 Number, ID8 Number, constraint pk_test_pk primary key (ID1,ID2,ID3,ID4,ID5,ID6));

Table created.

TEST:ABHAY> Begin for i in 1..100 loop insert into Test_PK values(i,i+1,i+2,i+3,i+4,i+5,i+6,i+7); end loop;end;
  2  /

PL/SQL procedure successfully completed.

TEST:ABHAY> commit;

Commit complete.

TEST:ABHAY> select count(*) from test_pk;

            COUNT(*)
--------------------
                 100

TEST:ABHAY> insert into test_pk select ID1,ID2,ID3,ID4,50,51,id7,id8 from
  2  test_pk t1 where ID4 Between 33 and 66 and ID6 between 34 and 67;
insert into test_pk select ID1,ID2,ID3,ID4,50,51,id7,id8 from
*
ERROR at line 1:
ORA-00001: unique constraint (ABHAY.PK_TEST_PK) violated


TEST:ABHAY> insert into test_pk
  2  select ID1,ID2,ID3,ID4,50,51,id7,id8
  3  from
  4  test_pk t1
  5  where (ID4 Between 33 and 66) and (ID6 between 34 and 67) and
  6  not exists ( select null from test_pk t2 where t1.id1=t2.id1 and t1.id2=t2.id2 and t1.id3=t2.id3 and
  7  t1.id4=t2.id4 and t1.id5=50 and t1.id6=51 );

32 rows created.

TEST:ABHAY> commit;

Commit complete.
You see below the way query's used which will exclude the row that will error while inserting.

Code:
TEST:ABHAY> select count(*) from ( select ID1,ID2,ID3,ID4,50,51,id7,id8
  2  from
  3  test_pk t1
  4  where (ID4 Between 33 and 66) and (ID6 between 34 and 67) and
  5  not exists ( select null from test_pk t2 where t1.id1=t2.id1 and t1.id2=t2.id2 and t1.id3=t2.id3 and
  6  t1.id4=t2.id4 and t1.id5=50 and t1.id6=51 ) );

            COUNT(*)
--------------------
                  32

TEST:ABHAY> select count(*) from ( select ID1,ID2,ID3,ID4,50,51,id7,id8
  2  from
  3  test_pk t1
  4  where (ID4 Between 33 and 66) and (ID6 between 34 and 67)
  5  );

            COUNT(*)
--------------------
                  33

TEST:ABHAY> select ID1,ID2,ID3,ID4,50,51,id7,id8
  2  from
  3  test_pk t1
  4  where (ID4 Between 33 and 66) and (ID6 between 34 and 67)
  5  minus
  6  select ID1,ID2,ID3,ID4,50,51,id7,id8
  7  from
  8  test_pk t1
  9  where (ID4 Between 33 and 66) and (ID6 between 34 and 67) and
 10  not exists ( select null from test_pk t2 where t1.id1=t2.id1 and t1.id2=t2.id2 and t1.id3=t2.id3 and
 11  t1.id4=t2.id4 and t1.id5=50 and t1.id6=51 ) ;

                 ID1                  ID2                  ID3                  ID4                   50                   51                  ID7                  ID8
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
                  46                   47                   48                   49                   50                   51                   52                   53
Abhay.