[
https://issues.apache.org/jira/browse/DERBY-4452?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bryan Pendleton updated DERBY-4452:
-----------------------------------
Component/s: (was: SQL)
(was: JDBC)
Documentation
Priority: Minor (was: Major)
Issue Type: Improvement (was: Bug)
Summary: Enhance docs to clarify handling of internal id counter for
generated by default keys (was: possible inconsistent internal id counter for
generated keys)
I'm not really sure what part of the docs was unclear, Martin.
On the page you referenced, just after the example you included,
http://db.apache.org/derby/docs/10.5/ref/rrefsqlj37836.html#rrefsqlj37836
the documentation says:
Note that unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT
column does not guarantee uniqueness. Thus, in the above example, the hi
and salut rows will both have an identity value of "1", because the
generated
column starts at "1" and the user-specified value was also "1". To
prevent
duplication, especially when loading or importing data, create the table
using
the START WITH value which corresponds to the first identity value that
the
system should assign. To check for this condition and disallow it, you
can use
a primary key or unique constraint on the GENERATED BY DEFAULT identity
column.
This seems pretty explicit to me.
Can you suggest some alternate or additional text that would have made the
behavior more clear to you?
> Enhance docs to clarify handling of internal id counter for generated by
> default keys
> -------------------------------------------------------------------------------------
>
> Key: DERBY-4452
> URL: https://issues.apache.org/jira/browse/DERBY-4452
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.5.3.0
> Environment: derby 10.5.3.0, jdbc, embedded db
> Reporter: Martin D.
> Priority: Minor
>
> 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.
