Oracle-L: RE: Unique constraint

From: Richard Huntley <rhuntley_at_mindleaders.com>
Date: Fri, 21 Jun 2002 12:03:22 -0800
Message-ID: <F001.00484857.20020621120322@fatcity.com>

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-----
Sent: Friday, June 21, 2002 12:21 PM
To: Multiple recipients of list ORACLE-L

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.
jerome.whittle_at_scott.af.mil
618-622-4145

-----Original Message-----

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Richard Huntley
INET: rhuntley_at_mindleaders.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jun 21 2002 - 15:03:22 CDT