Hi,
I am doing the following things:
SQL> create table xx_con(col1 number);
Table created.
SQL> insert into xx_con values(1);
1 row created.
SQL> insert into xx_con values(1);
1 row created.
SQL> insert into xx_con values(2);
1 row created.
SQL> alter table xx_con add constraint col1_uk unique(col1) enable validate;
alter table xx_con add constraint col1_uk unique(col1) enable validate
*
ERROR at line 1:
ORA-02299: cannot validate (APPS.COL1_UK) - duplicate keys found
This is exactly the same as the previous one.As per the documentation,when we specify "ENABLE VALIDATE" , all the existing and new data should comply with the constraint.But since the data violates the constraint,the constraint is not created .
SQL> drop table xx_con;
Table dropped.
SQL> create table xx_con(col1 number);
Table created.
SQL> insert into xx_con values(1);
1 row created.
SQL> insert into xx_con values(1);
1 row created.
SQL> insert into xx_con values(2);
1 row created.
SQL> alter table xx_con add constraint col1_uk unique(col1) enable novalidate;
alter table xx_con add constraint col1_uk unique(col1) enable novalidate
*
ERROR at line 1:
ORA-02299: cannot validate (APPS.COL1_UK) - duplicate keys found
As per the documentation,when we specify "ENABLE NOVALIDATE", all the DML operations performed after this should comply with the constraint.But there can be existing data which voilates the constraint.If this is the case then why it is erroring out?
please clarify this.