help me out

[vennai]

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.

[arjun]

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.

[vennai]

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

[koushik]

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

[arjun]

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.

[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.

[arjun]

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.