Re: unique constraint error

TurkBear wrote:

> 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 =-----

Thanks All.

Daniel Morgan Received on Tue Jul 23 2002 - 17:27:28 CEST