db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Malte.Kem...@de.equens.com>
Subject AW: primary key constraint violation on autoincrement id
Date Wed, 09 Dec 2009 09:30:35 GMT
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.

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