Ben Forta

Like many DBMSs, Apache Derby (included with ColdFusion 8) supports identity fields – fields that auto-increment each time a row is add. These are commonly used for primary key values, as the DBMS itself ensures that these values are unique and never reused.
One problem with identity fields is that sometimes you may need to insert a row providing an explicit value to be used, essentially overriding identity functionality. Some DBMSs (like SQL Server) allow you to turn off identity processing with an explicit directive while a row is inserted and then turn it on again, and numbering automatically continues from the new highest value. Other DBMSs (like MySQL) allow you to simply insert values with specific values and, if present, these are used instead of auto generated values.
Derby also supports identity fields. These can be defined as ALWAYS in which case Derby always generates the value (which can never be manually specified), or BY DEFAULT in which case identity fields are generated only if an explicit value is not provided.
It’s a rather nice implementation. But, it does not work as you’d expect. You can indeed specify an explicit value if BY DEFAULT is used, and your value will be used. But Derby does not seem to pay attention to explicitly provided values and does not update the internal counters accordingly, so when you next insert a row without an explicit value it may generate the exact same value as the one you specified. And if that column is a primary key, well, obviously the second INSERT is going to fail.
There is a workaround. When the table is created you may specify an optional START WITH value. So, if you need to load the table with 25 rows you can set START WITH to 26, and that will be the starting point for generated identity values. I guess you could also START WITH some really high number, and reserve the lower values for when you needed to explicitly provide a value.
Still, this is a hack, and it makes BY DEFAULT rather useless, which is a shame.