Re: unique constraint error

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 Fri Jul 19 2002 - 11:03:03 CDT