URGENT: Adding a unique constraint with novalidate option

[rmgirma]

Hello,
I need to add a unique constraint on table U_SMCSHIPPING column SERVICER_LN_NUM . I know there are presently rows that violates this constraint (meaning, there are duplicate rows).
I used the following statement and got an error message.

ALTER TABLE EMPOWER.U_SMCSHIPPING ADD CONSTRAINT CHASE_NUM_UNIQUE UNIQUE(SERVICER_LN_NUM) ENABLE NOVALIDATE
*
ORA-02299: cannot validate (EMPOWER.CHASE_NUM_UNIQUE) - duplicate keys found

Why I am getting this error msg when I'm explicitely telling it not to validate. All I want is to prevent future duplications.
Please help. it's very urgent.
Oracle 8i version 8.1.6 Enterprise Edition.

Thanks in advance for your help
Robel Girma

[Oracledba8]

As soon as you enable the constraint, it will check the Current records as well,

Better to use Disable, instead of enable.

Thanks

[rmgirma]

Thanks for the quick reply but if I use Disable, will future duplicate inserts be prevented? I think the table will allow duplicate rows.
Also, I went ahead and created it with the disable clause as suggested and tried to enable it with the novalidate option:
ALTER TABLE EMPOWER.U_SMCSHIPPING ENABLE NOVALIDATE CONSTRAINT CHASE_NUM_UNIQUE;

Got the same error msg:
ORA-02299: cannot validate (EMPOWER.CHASE_NUM_UNIQUE) - duplicate keys found

Thanks,
Robel

[Oracledba8]

Of Course when the Constraint is disable it will check the validity whenever it will be disabled and it will not prevent the Future dupicate rows.

Better to remove the dupicate rows, enable the Contraint and it will not have any duplicate rows in future.

Duplicate Selection?
Select * from table1 a
where rowid not in (select max(rowid) from table1 b
where a.col1=b.col1)

Table1 is Your Table... in Subquery all the columns that will be checked for duplication...

To Delete, suppose from emp table I want to remove dupicate empno...
Delete emp a
where rowid not in (select max(rowid) from emp b
where a.empno = b.empno);

Thanks

[rmgirma]

Maybe I was not clear in my previous postings.
The main purpose of me having to create a constraint with enable novalidate option is to ignore the values that are currently present in the table and to only apply the unique constrainst for future updates. This table has millions of rows and thousands of duplicates. (Duplicates were allowed with the old business rule and those records will need to be kept). Now that our business rules have changed, I do not want any duplicate rows to be inserted. How do I create this constraints? I know Oracle has this type of capability. I just can't get it to work.
Thanks,
Robel

[rmgirma]

According to Oracle documetations on OTN:

ENABLE NOVALIDATE
This setting ensures that all new DML operations on the constrained data comply with the constraint, but does not ensure that existing data in the table complies with the constraint.

If anyone had this working before, please post the steps you took for this to work.
Thanks in advance.

[gpsingh]

Hi ,

How it works:

Create table a ( a number);

alter table a add constraint gpu unique (a) deferrable initially deferred; -- Note this statement

alter table a disable constraint gpu ;

insert into a values (1);
insert into a values (1);
commit;

alter table a enable novalidate constraint gpu;
sql> table altered
sql>
select * from a;

A
-----
1
1
SQL> insert into a values (1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SYSTEM.GPU) violated

So the constraint has to be created as deferrable .

thanks
GP

[rmgirma]

THANK YOU !!!
That did it.
Robel.

[rmgirma]

FYI : The statement I used to create the constraint is:

alter table empower.u_smcshipping add constraint CHASE_NUM_UNIQUE UNIQUE(SERVICER_LN_NUM) deferrable initially deferred NOVALIDATE;

Result:
Statement processed.

[ajaybhat]

alter table <table_name> add constraint <constraint_name> unique (column_name) deferrable novalidate ;