db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stephan van Loendersloot (LIST)" <step...@republika.nl>
Subject Re: Auto Increment Constraint Error
Date Wed, 09 Jul 2008 09:23:44 GMT
Hello Albert,


You should restart the identity column with a value that is greater than 
the maximum value that it contains after the import.

First, select the maximum value + 1 that your table contains:

SELECT MAX(id) + 1 FROM t AS new_value

In your example you have id 1, 3, and 6, so the result of this query 
would be 7.

Then (pseudo code):

ALTER TABLE t ALTER COLUMN id RESTART WITH new_value


Where new_value, of course, is 7.


For more information, please see the docs:


http://db.apache.org/derby/docs/dev/ref/rrefsqlj81859.html#rrefsqlj81859__rrefsqlj37860


Regards,

    Stephan van Loendersloot.




Albert Kam wrote:
> Hello again Apache Derby !
>
> Lately i've met some errors that outputs like this :
> java.sql.SQLException: The statement was aborted because it would have 
> caused a duplica
> te key value in a unique or primary key constraint or unique index 
> identified by 'SQL080706164629120' defined
> on 'MST_GROUP'
>
> This happens only at times and not everytime.
>
> After several cases, i realize that this caused by this condition :
> 1. I create a new database and import the csv file
> 2. When i try to insert into the tables that has the autoincrement 
> primary key .. and the table is already filled by records imported by 
> the csv file, for example like this :
> -------------------------------------------------------
> id       |  name    | .......
> -------------------------------------------------------
> 2          Test
> 3          Blah     
> 6          Testing
>
> The first insert will succeed, as it will generate the 1st key, which 
> is 1 ..
> The second and the third will fail, as the keys are already existing, 
> imported previously from the csv file ..
> The fourth n fifth insert will succeed .. where as the sixth will fail 
> .. and the rest will succeed ..
>
> One example to reproduce this is :
> CREATE TABLE t (
>     id                   int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
>     name          varchar(30) NOT NULL,
>     PRIMARY KEY(id),
>     unique(name)
> );
> insert into t (id, name) values(2, 'albert'); <- assuming this is how 
> the csv import behaves :)
> insert into t (name) values('test1');
> insert into t (name) values('test2');
>
> The output is :
> ij> insert into t (id, name) values(2, 'albert');
> 1 row inserted/updated/deleted
> ij> insert into t (name) values('test1');
> 1 row inserted/updated/deleted
> ij> insert into t (name) values('test2');
> ERROR 23505: The statement was aborted because it would have caused a 
> duplicate key value in a unique or primary key constraint or unique 
> index identified by 'SQL080709131412860' defined on 'T'.
>
> What should i do to avoid this kind of error after importing from csv 
> files along with their keys ?
>
> Thank you !
>
> Regards,
> Albert Kam
>
> -- 
> Do not pursue the past. Do not lose yourself in the future.
> The past no longer is. The future has not yet come.
> Looking deeply at life as it is in the very here and now,
> the practitioner dwells in stability and freedom.
> (Thich Nhat Hanh) 

Mime
View raw message