[jira] Commented: (DERBY-4452) possible inconsistent internal id counter for generated keys

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4452?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12780569#action_12780569 ] 

Bryan Pendleton commented on DERBY-4452:
----------------------------------------

Yes, that is how GENERATED BY DEFAULT works:
http://db.apache.org/derby/docs/10.5/ref/rrefsqlj37836.html#rrefsqlj37836

"An identity column that is GENERATED BY DEFAULT will only increment 
and use the default value on insertions when no explicit value is given."

If you wish to reset the internal counter, after having inserted some rows
with column values which you set yourself, you can use ALTER TABLE ... RESTART WITH ...
http://db.apache.org/derby/docs/10.5/ref/rrefsqlj81859.html

> possible inconsistent internal id counter for generated keys
> ------------------------------------------------------------
>
>                 Key: DERBY-4452
>                 URL: https://issues.apache.org/jira/browse/DERBY-4452
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.5.3.0
>         Environment: derby 10.5.3.0, jdbc, embedded db
>            Reporter: Martin D.
>
> it seem that the internal auto id counter for generating the keys (ids) is not updated when a value with a fix id is inserted.
> sample code:
> try {
> 	Connection con = DriverManager.getConnection("jdbc:derby:db/data/tmp;create=true");
> 	Statement stmt = con.createStatement();
> 	stmt.executeUpdate(
> 		"CREATE TABLE testtable (id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, value INT NOT NULL, PRIMARY KEY(ID))");
> 	// insert a few test values with auto id
> 	stmt.execute("INSERT INTO testtable (value) VALUES (1),(2),(3)");
> 	// insert more values with fix id
> 	stmt.execute("INSERT INTO testtable (id,value) VALUES (4,4),(5,5),(6,6)");
> 	// try to insert more values with auto id
> 	// -> will throw SQLIntegrityConstraintViolationException (SQLState 23505)
> 	stmt.execute("INSERT INTO testtable (value) VALUES (7)");
> 	stmt.close();
> 	con.close();
> } catch (Exception e) {
> 	e.printStackTrace();
> }

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.