Trying to nudge this forward a bit: The CREATE TABLE statement fails for
me because the constraint name contains an illegal character ('='). Are
you sure that this is the table definition? Perhaps there are other
discrepancies in this CREATE TABLE statement. For instance, maybe the
identity column was declared GENERATED BY DEFAULT rather than GENERATED
ALWAYS. If the column was GENERATED BY DEFAULT, then a user can override
the value of the identity column and insert duplicate keys, triggering
the constraint violation.
Hope this helps,
-Rick
[email protected] wrote:
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=5FBOOKMARK' 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=5FBOOKMARK' 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(Unkno=
wn
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 =3D "INSERT INTO BOOKMARK (description, saveditems)
VALUES (?, ?)";
statement =3D connection.prepareStatement(sql, PreparedStatemen=
t.
RETURN=5FGENERATED=5FKEYS);
statement.setString(1, description);
statement.setInt(2, savedItems);
if (statement.executeUpdate() =3D=3D 1)
{
// Get the new id field
ResultSet generatedKeys =3D 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=5FBOOKMARK PRIMARY KEY (id)
);
Any ideas?
Thanks,
Stuart
Freecall 1800 447 111
http://www.esriaustralia.com.au
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Registration for OZRI / APUC 2010 now open
Join us on the Gold Coast on 3 - 5 March 2010
www.esriaustralia.com.au/ozri2010
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Putting knowledge into place=AE
This message is intended for the addressee named and may contain confidenti=
al information. If you are not the intended recipient please notify the sen=
der by return email, do not use or disclose the contents and delete the mes=
sage and any attachments from your system.
**Please consider the environment before printing this email**
