URGENT: Adding a unique constraint with novalidate option
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: URGENT: Adding a unique constraint with novalidate option

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    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

  2. #2
    Join Date
    Feb 2001
    Posts
    184
    As soon as you enable the constraint, it will check the Current records as well,

    Better to use Disable, instead of enable.

    Thanks

  3. #3
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85

    Thanks for the quick reply but...

    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

  4. #4
    Join Date
    Feb 2001
    Posts
    184
    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

  5. #5
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    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

  6. #6
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    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.



  7. #7
    Join Date
    Feb 2001
    Posts
    389
    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

  8. #8
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    THANK YOU !!!
    That did it.
    Robel.

  9. #9
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    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.

  10. #10
    Join Date
    Feb 2013
    Posts
    1
    alter table <table_name> add constraint <constraint_name> unique (column_name) deferrable novalidate ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width