db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: primary key constraint violation on autoincrement id
Date Wed, 09 Dec 2009 13:56:17 GMT
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

smcintyre@esriaustralia.com.au 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**
>   


Mime
View raw message