Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (athena.apache.org: 83.137.146.145 is neither permitted
 nor denied by domain of stephan@republika.nl)
Message-ID: <487483A0.7000000@republika.nl>
MIME-Version: 1.0
Date: Wed, 09 Jul 2008 11:23:44 +0200
From: "Stephan van Loendersloot (LIST)" <stephan@republika.nl>
Organization: REPUBLiKA B.V.
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Auto Increment Constraint Error
References: <7b813ada0807082318i41d58cb0pf785771af394fc7a@mail.gmail.com>
In-Reply-To: <7b813ada0807082318i41d58cb0pf785771af394fc7a@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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)