Try the next with an H2DB you don't mind needing to remove. WARNING. This Can DAMAGE THE DATABASE IRREVERSIBLY!

  1. Produce a table with self referential default column. E.g., use alter table to change an regular table:

    create table if not exists BRICK_H2( ID tinyint );
    alter table BRICK_H2 alter column ID set default ifnull(
        (select max(ID) from BRICK_H2 for update)+1,0
    );
    
  2. Close connections and shutdown database.

  3. Start database again and then try to connect. Observe connection failure at error:


Table "BRICK_H2" not found SQL statement:

CREATE CACHED TABLE PUBLIC.BRICK_H2(

    ID TINYINT DEFAULT IFNULL(((Choose

    MAX(ID)

FROM PUBLIC.BRICK_H2

    /* PUBLIC.BRICK_H2.tableScan */

FOR UPDATE) + 1), )

) [42102-155] 42S02/42102 (Help)


BTW. Do not respond "use auto_increment". Yes, I understand about auto_increment posts. Auto increment posts leave gaps after rollback and do not cope with all datatypes. For instance how do you auto increment a column in which the application uses an formula that's not "give a constant integer?" Also, there's not a way to semsibly auto_increment a VARCHAR, however your application may have a wonderfully sensible notion.

An answer is by using a Java function, for example:

drop all objects;
create table if not exists do_not_brick(id int);
create alias query as $$
String query(Connection conn, String sql) throws SQLException { 
  ResultSet rs = conn.createStatement().executeQuery(sql); 
  rs.next(); 
  return rs.getString(1);
}$$;
alter table do_not_brick alter column id set default
ifnull(query('select max(id) from do_not_brick for update')+1, 0);
insert into do_not_brick() values(), (), ();
select * from do_not_brick;

Incidentally, 'brick' is relative... you are able to still retrieve the information while using Recover tool. However it isn't nice, and will also be fixed within the next release. However , H2 does not restrict you skill within the default clause. Other databases simply do not let questions within the default clause, however i think that's timid (is the best word?), I'll try to look for a much better solution (possibly take). Exactly what do you suggest?