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: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 23 Jul 2002 10:30:26 +1000
Message-ID: <ahi81b$ion$1@lust.ihug.co.nz>


'Bout sums it up!

Plus the one that finally gets fixed in 9i, but is a pain until then: the propensity of unique indexes to go walkabout at the drop of a hat. Disable a constraint enforced with one, and the unique index is dropped. Use a (just as effective) non-unique index, and it isn't.

Plus you can't run a constraint in 'enabled novalidate' state, whether deferrable or not (two different issues).

And contrary to one comment I've seen here, there is zero performance impact from using a non-unique index as opposed to a unique one for such constraint enforcement.

Regards
HJR "Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:MW0%8.41460$Hj3.124666_at_newsfeeds.bigpond.com...
> Hi Daniel,
>
> Effectively, by creating a unique index on a table your are implementing a
> unique constraint on a table. So I guess from that perspective there is no
> difference.
>
> However, in my opinion there are (at least) two good reasons for not
> creating a unique index directly:
>
> 1) Even though the table effectively has a constraint on it and is
therefore
> implementing a business rule, this is *not* documented in the DD in the
> constraint views. If you were to look in dba_constraints, it would appear
> that there is no such constraint on your unique column. This is a pain and
a
> potential trap for the unwary.
>
> 2) You lose flexibility in the *type* of unique (or primary key)
constraint
> you wish to implement. You can't implement a deferrable constraint if a
> unique index exists already on the column. This is a pain and a potential
> trap for the unwary.
>
> By implementing a unique or primary key constraint directly, Oracle will
> automatically create an appropriate index (unique/non unique) AND Oracle
> will document all the necessary info in the DD.
>
> I always question the creation of a unique index for these reasons.
>
> Regards
>
> Richard
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D3C9353.B151C88C_at_exesolutions.com...
> > "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
> > > =-----
> >
> > 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
> > unique index and a unique constraint other than the syntax that creates
> it.
> >
> > Daniel Morgan
> >
>
>
Received on Mon Jul 22 2002 - 19:30:26 CDT

Original text of this message

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