db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: AW: primary key constraint violation on autoincrement id
Date Wed, 09 Dec 2009 09:50:01 GMT
Malte.Kempff@de.equens.com wrote:
> Hi Stuart,
> I think you should write logs then you may are able to analyse it this is a problem of
your program or not.
>
> May be the ID you are using as primary key is not as unique as you think.
>   

Maybe I'm wrong, but in this case I think Derby is providing / 
generating the unique key.
I don't have any experience debugging this, but let's hope someone else 
chimes in.

Stuart, is theapplication multi threaded, or does it happen when you 
have only one connection to the database?
Are you able to reproduce this rather consistently if you run only the 
specific insert from your application?


Regards,
-- 
Kristian

> Good luck and best regards
>
> Malte
>
>
> -----Urspr├╝ngliche Nachricht-----
> Von: smcintyre@esriaustralia.com.au [mailto:smcintyre@esriaustralia.com.au] 
> Gesendet: Mittwoch, 9. Dezember 2009 04:18
> An: derby-user@db.apache.org
> Betreff: primary key constraint violation on autoincrement id
>
>
> Hi,
>
> When I try to insert into a table that has an autoincrement primary key, I
> get the following error:
>
> 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
> 'PK_BOOKMARK' defined on 'BOOKMARK'.
> java.sql.SQLIntegrityConstraintViolationException: 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 'PK_BOOKMARK' defined
> on 'BOOKMARK'.
>       at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
>       at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source)
>       at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
>  Source)
>       at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
>       at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> Source)
>       at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> Source)
>       at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
> Source)
>       at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
> Source)
>       at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown
> Source)
>       at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(
> DelegatingPreparedStatement.java:102)
>       ...
>
> I'm using Derby 10.5.3 in embedded mode.
>
> I don't get the issue every time I run the insert and I'm not sure why.
>
> The insert code looks like this.. and insert only happens in one place in
> the application:
>
>             String sql = "INSERT INTO BOOKMARK (description, saveditems)
> VALUES (?, ?)";
>             statement = connection.prepareStatement(sql, PreparedStatement.
> RETURN_GENERATED_KEYS);
>
>             statement.setString(1, description);
>             statement.setInt(2, savedItems);
>
>             if (statement.executeUpdate() == 1)
>             {
>                 // Get the new id field
>                 ResultSet generatedKeys = statement.getGeneratedKeys();
>                 generatedKeys.next();
>                 vo.setId(generatedKeys.getInt(1));
>             }
>
>
> The create statement for the table is:
> CREATE TABLE BOOKMARK (
>       id int GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)
> NOT NULL,
>       description varchar(50),
>       saveditems int DEFAULT 0 NOT NULL,
>       CONSTRAINT PK_BOOKMARK PRIMARY KEY (id)
> );
>
>
> Any ideas?
>
> Thanks,
> Stuart
>
> Freecall 1800 447 111
> http://www.esriaustralia.com.au
>
> ============================================
> Registration for OZRI / APUC 2010 now open
> Join us on the Gold Coast on 3 - 5 March 2010
> www.esriaustralia.com.au/ozri2010
> ============================================
>
> Putting knowledge into place®
>
> This message is intended for the addressee named and may contain confidential information.
If you are not the intended recipient please notify the sender by return email, do not use
or disclose the contents and delete the message and any attachments from your system.
>
>
> **Please consider the environment before printing this email**
>   


Mime
View raw message