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

> -----Original Message-----
> From: MARCOTULLI ARMANDO [SMTP:AMarcotulli_at_eulergroup.com]
>
> 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: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

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