Re: unique constraint error

"Howard J. Rogers" wrote:

> And Oracle's own documentation advises you not to create Unique indexes.
>
> Regards
> HJR
>
> "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
> =-----

[Quoted] [Quoted] Not disputing your statement ... but I'd be interested in the "Why"?

Which sort of leads to the question of whether there is any difference between a [Quoted] unique index and a unique constraint other than the syntax that creates it.

Daniel Morgan Received on Tue Jul 23 2002 - 01:21:57 CEST