help me out
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: help me out

  1. #1
    Join Date
    Aug 2001
    Posts
    35

    help me out

    table balances
    (SSN
    BALANCE_TYPE_ID
    BALANCE_NAME
    BALANCE_DIMENSION_ID
    JURISDICTION_CODE
    AMOUNT
    FLAG )


    i want to enforce unique constraint on the above table , for the combination of ssn and balance_type_id,
    but since the combiantion has dupilcate values, i am not able to enforce it.is there any way
    to find duplicate values for this particular unique combination,so that i can delate it and enforce the unique
    constraint.

  2. #2
    Join Date
    Feb 2003
    Location
    Kolkata, India
    Posts
    33

    Smile

    I presume you may use the following as it will be more efficient than using “Unique Row Detection” sql(s),

    ALTER TABLE SCHEMA.TABLENAME
    ENABLE NOVALIDATE CONSTRAINT U_SSN_BALANCE_TYPE_ID
    UNIQUE (SSN,BALANCE_TYPE_ID);

    This statement won’t validate your existing data for unique key violations..

    Next you have to create an exception table (using %ORACLE_HOME%\RDBMS\ADMIN\utlexcpt.sql)

    Then ,

    ALTER TABLE SCHEMA.TABLENAME
    ENABLE VALIDATE CONSTRAINT U_SSN_BALANCE_TYPE_ID
    EXCEPTIONS INTO SYSTEM.EXCEPTIONS;

    Once you process the above atstement check out the “SYSTEM.EXCEPTIONS” table for rows that violated your constraint.

    Take necessary actions and enable your constraint.

  3. #3
    Join Date
    Aug 2001
    Posts
    35
    Hi
    thanks for your reply.
    now when i am trying to validate the constraint it is giving

    The following error has occurred:

    ORA-02299: cannot validate (XXUAB.SSN_TYPE_ID_UNIQUE) - duplicate keys found

  4. #4
    Join Date
    Feb 2003
    Posts
    2
    first delete duplicate rows from your table-:

    delete from table_name
    where rowid not in (select min(rowid) from table_name group by SSN,BALANCE_TYPE_ID)

    then alter the table adding unique constraint-:

    alter table table_name
    add constraint constraint_name unique(SSN,BALANCE_TYPE_ID);

    hope it solves ur prob

  5. #5
    Join Date
    Feb 2003
    Location
    Kolkata, India
    Posts
    33
    Once you have the constraint violating rows in your EXCEPTION table you have the option open whether to delete then or to go for any corrective actions. But do necessary corrections before you try to re-enforce your unique-key constraint again. Deletion statement suggested by my friend above sounds a bit drastic.

  6. #6
    Join Date
    Feb 2003
    Posts
    2
    when the whole purpose is to delete the duplicate values before enabling unique constraint ,i would be grateful to know what necessary corrective action you need to take.

  7. #7
    Join Date
    Feb 2003
    Location
    Kolkata, India
    Posts
    33
    Originally posted by koushik
    when the whole purpose is to delete the duplicate values before enabling unique constraint ,i would be grateful to know what necessary corrective action you need to take.
    I believe in keeping my options open my friend. The issue is not whether you should delete but what different options do you have.

    1. If the table contained only two columns then I would have gone with deletion because there would not be any sort of info loss. But in the above cases when initially you didn’t enforce a constraint there is bound to be info loss in case of deletion just because the other columns in the duplicate rows may have different values.
    2. Deletion may (or may not) have other effects vis-à-vis you have a database trigger associated with the base table.
    I can go on and on and on…..

    Please let me know if you feel otherwise.
    ("`-''-/").___..--''"`-._
    `6_ 6 ) `-. ( ).`-.__.`)
    (_Y_.)' ._ ) `._ `. ``-..-'
    _..`--'_..-_/ /--'_.' ,'
    (((' (((-((('' ((((

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