Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Unique Key

Re: Unique Key

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 28 Jul 1999 20:22:28 +0100
Message-ID: <933191110.6012.2.nnrp-03.9e984b29@news.demon.co.uk>

You're right -
I've just run this on 8.0.4

create table t1 (n1 number , v1 varchar2(20)); create index i1 on t1(n1);
alter t1 add constraint t_pk primary key (n1);

insert into t1 values (1,'a');
insert into t1 values (1,'a');

The second insert give error 0001 - violation of primary key DEMO.T_PK It's cute the way oracle discovers the available index.

A huge benefit here is in SQL*Loading incremental data with the strategy:

    disable constraint

        (which WON'T drop the non-unique index)     sql*load potentially illegal data
    enable constraint exceptions into

        which will use the index, quite quickly to find bad rows

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Pete Sharman wrote in message <379F250A.6C7C6818_at_us.oracle.com>...
>Actually, Oracle CAN check uniqueness with a non-unique index as well. I
think
>this came in 8.0, but I could be wrong on that. If you think about it, the
>fact that the value already exists in the index is what's important, not
the
>number of times it occurs in the index (anything greater than zero proves
it's
>already there).
Received on Wed Jul 28 1999 - 14:22:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US