Primary key validation for new rows only ......

Anuj:orcl>create table test ( a number );

Table created.

Anuj:orcl>insert into test ( a ) values ( 1 ) ;

1 row created.

Anuj:orcl>/

1 row created.

Anuj:orcl>/

1 row created.

Anuj:orcl>insert into test ( a ) values (2 ) ;

1 row created.

Anuj:orcl>alter table test add constraint test_pk primary key ( a ) novalidate ;
alter table test add constraint test_pk primary key ( a ) novalidate
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.test_PK) - primary key violated


Anuj:orcl>create index test_i on test ( a );

Index created.

Anuj:orcl>alter table test add constraint test_pk primary key ( a ) novalidate ;

Table altered.

Anuj:orcl>select * from test ;

A
----------
1
1
1
2

Anuj:orcl>insert into test ( a ) values (2 ) ;
insert into test ( a ) values (2 )
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.test_PK) violated