In a previous post, I described the SQL command to create a table with an auto increment column in the Derby database. It turns out that the 'GENERATED' keyword can be used in a couple of way.

• GENERATED ALWAYS AS IDENTITY
• GENERATED BY DEFAULT AS IDENTITY

For details see the relevant section of the Derby manual here. Essentially the distinguishing fact being - by using 'BY DEFAULT' the user has the ability to specify a value for the identity column as part of a (insert) SQL statement, whereas with 'ALWAYS', the column can never be explicitly edited by the user. Also 'ALWAYS' guarantees uniqueness, but 'BY DEFAULT' does not (use the UNIQUE/PRIMARY KEY attribute to achieve the same effect though)

So, the better suited SQL statement for creating a table in Derby in a RoR application would be

CREATE TABLE ${table-name} ( 
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    ${other-columns-and-constraints} )

Running a simple RoR application with the underlying identifier created via either option works without any issues. However, if you want to run the tests that comes in built with Rails framework the difference is detrimental (since the test framework seems to explicitly want to insert a value for the identifier column).