Re: unique constraint error

The unique constraint error can also be generated during an UPDATE clause.

So I dont think this code will work, am i wrong ?

"Daniel Morgan" <dmorgan_at_exesolutions.com> a écrit dans le message news: 3D3837FB.4ED998F2_at_exesolutions.com...
> Cel 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
>
> BEGIN
> INSERT INTO ...
>
> EXCEPTION
> WHEN OTHERS THEN
> UPDATE ...
> END;
>
> But I would seriously consider dumping those CHAR fields, except perhaps
the
> CHAR(1) as they will likely cause you great misery for a long long time if
there
> will be trailing spaces.
>
> Daniel Morgan
>
Received on Mon Jul 22 2002 - 03:16:57 CDT