db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Martin D. (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4452) possible inconsistent internal id counter for generated keys
Date Fri, 20 Nov 2009 15:09:39 GMT

    [ 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.


Mime
View raw message