[
https://issues.apache.org/jira/browse/DERBY-4452?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12780586#action_12780586
]
Martin D. commented on DERBY-4452:
----------------------------------
Thanks for your quick reply!
Indeed, this is the usual behavior. But the reference manual could contain a
notice or hint that the way I tried it is unlikely to succeed :( Instead, the
example code ...
(http://db.apache.org/derby/docs/10.5/ref/rrefsqlj37836.html#rrefsqlj37836)
" create table greetings (i int generated by default as identity, ch char(50));
-- specify value "1":
insert into greetings values (1, 'hi');
-- use generated default
insert into greetings values (DEFAULT, 'salut');
-- use generated default
insert into greetings(ch) values ('bonjour'); "
... implies that everything will be ok.
Also the different behavior of other database systems (like MySQL) made me
think that it was a bug...
Regards, Martin
> 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.
