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 constraint error

Re: unique constraint error

From: TurkBear <jgreco1_at_mn.rr.com>
Date: Tue, 23 Jul 2002 08:52:51 -0500
Message-ID: <7knqju0bfn6k8i9to2rml4c5p4l279jd0m@4ax.com>

My statement reveals the problem of using ' intuitive logic' instead of information when replying to a question..

It seemed logical that if the index had to 'keep track of' only one location for a particular value, it would be faster to find that location -

Oh Well, so much for intuitive thoughts without actual data to support it..

Sorry..

.

"Richard Foote" <richard.foote_at_bigpond.com> wrote:

>Hi TurkBear,
>
>Why is a unique index faster than a non unique index ?
>
>I'm all ears.
>
>Richard
>"TurkBear" <jgreco1_at_mn.rr.com> wrote in message
>news:ps2ojuo8ru3p2kjnmqihgame31qu6pj8nb_at_4ax.com...
>>
>> In general it is a short way to create both an index and constraint..
>> A Unique index is usually faster than a non-unique one the unique
>constraint enforces business rules about your data and
>> helps maintain 'normalized' data in a RDBMS by not allowing the accidental
>insertion or creation of duplicate keys..
>>
>>
>>
>> "Cel" <cefernan_at_capgemini.fr> wrote:
>>
>> >so what exactly is a unique index ?
>> >what's it used for ?
>> >
>> >"TurkBear" <jgreco1_at_mn.rr.com> a écrit dans le message news:
>> >8qigju41jlklidlreeh9f480tvgmfhfk2e_at_4ax.com...
>> >>
>> >> Your procedure should check for the record and then do either an update
>or
>> >an Insert..
>> >>
>> >> This is not an error, it is doing exactly what a Unique constraint is
>> >supposed to do..
>> >>
>> >> (BTW, cross-posting is not usually a good idea)
>> >>
>> >>
>> >> "Cel" <cefernan_at_capgemini.fr> wrote:
>> >>
>> >> >I have a table A :
>> >> >
>> >> >CREATE TABLE A (
>> >> >column1 VARCHAR2 (2) NOT NULL, column2 VARCHAR2 (4) NOT NULL, column3
>> >> >VARCHAR2 (2) NOT NULL,
>> >> >
>> >> >column4 VARCHAR2 (6) NOT NULL, column5 CHAR (8) NOT NULL, column6 CHAR
>> >(8),
>> >> >
>> >> >column7 CHAR (1), column8 CHAR (1))
>> >> >
>> >> >There are 5 index on the first 5 columns:
>> >> >CREATE UNIQUE A_IU ON "SCHEMA".A(column1 , column2 , column3 , column4
>,
>> >> >column5 )
>> >> >
>> >> >TABLESPACE IDX_APP_A PCTFREE 5 STORAGE(INITIAL 16384 NEXT 8192
>> >PCTINCREASE
>> >> >0 ) ;
>> >> >
>> >> >I have a stored procedure which does either an INSERT :
>> >> >
>> >> >INSERT INTO A ( column1 , column2 , column3 , column4 , column5 ,
>> >column6
>> >> >, column7 , column8 )
>> >> > VALUES ( val1, val2, val3, val4, val5, val6, NULL, NULL , NULL);
>> >> >
>> >> >or an UPDATE :
>> >> >UPDATE A SET column6 = val11 AND column3 = val3 AND column4 = val4;
>> >> >
>> >> >Of course if the record already exists in the table,
>> >> >it raises a unique constraint error.
>> >> >
>> >> >How do i do to avoid this error ?
>> >> >thanks for your help
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
>> >==----------
>> >> http://www.newsfeed.com The #1 Newsgroup Service in the World!
>> >> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
>> >=-----
>> >
>>
>>
>>
>> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
>==----------
>> http://www.newsfeed.com The #1 Newsgroup Service in the World!
>> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
>=-----
>

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Tue Jul 23 2002 - 08:52:51 CDT

Original text of this message

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