db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4452) Enhance docs to clarify handling of internal id counter for generated by default keys
Date Wed, 02 Dec 2009 22:54:20 GMT

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


Mime
View raw message