RE: Unique constraint

[Richard Huntley]

Title: RE: Unique constraint

I would agree, having a unique constraint delete records would cause big problems, especially if only the column(s) in the unique key were

duplicated but the other values for the record were different and the wrong one was deleted.

 

The use of "exceptions into" allows YOU to capture information about the records causing the constrain creation to fail (they get dumped into

the table you specify).  So, if you know that all data for the records were duplicated you could simply specify "exceptions into EXCEPTIONS"

when attempting to create to the unique key.  Then, you could get rid of the duplicates via:

 

delete dwd6

where col1 = 'some value'

and rowid not in (select min(row_id) from EXCEPTIONS)

/

 

This will delete all but one of the records and thus allow you to build the unique key.

 

-----Original Message-----
From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 21, 2002 12:21 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Unique constraint

Armando,

I certainly would not want the enabling of a unique constraint to delete records even if they are duplicated in a column or two!

Exception helps you identify which records are duplicates so you can then handle the problem as you feel best.

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145

-----Original Message-----
From:   MARCOTULLI ARMANDO [SMTP:[EMAIL PROTECTED]]

Hi,

I have some problem regarding the unique constraint and I would like to get
some ideas in order to solve a dup key problem.

Let's say I have a table with two columns: col1, col2.
In this table there are some rows with the same value for col1.

If I try this:
alter table dwd6 add constraint uq_dwd6
unique (col1)
using index pctfree 0
tablespace dwdix001
exceptions into bad_dwd6;

then I get the following error:
ORA-02299: cannot validate (TSSY01.UQ_DWD6) - duplicate keys found

My doubt is:
If Oracle can't discard duplicate keys when adding a unique constraint for
what purpose is it available the "expeception" clause?

Thanks a lot.

Armando