Unanswered: Can one create an Auto increment SQL function for Numbers other than integer ?
My second post to dbForums. I am a newbie to db design so sorry for such a simple question.
In every example I have seen, an auto incrementing number is always an integer. Is there any reason I cannot auto increment a 'tinyint' (byte) ?
I realise that support for auto incrementing numbers is done differently depending on the target RDBMS, eg IDENTITY for ms SQL server. I have tentatively decided on HSQLDB V2 with LO/OO0 'Base' as front end.
I want to use a number of small tables containing short lists (typically <100 rows), each containing fixed enumerated data. I thought it a good idea to keep all number types just sufficiently large to hold the max range of values as a) it is logical, b) may save space, c) may improve execution time. I therefore want to use a 'tinyint' as the Key to each of those small lookup tables.
Once an enumeration is defined, am unlikely to delete it.
During the early period using the db, I will not have anticipated all of the enumerated types, and so whenever I add new data to the main table not of a class belonging to an existing enumerated type, in the main data entry Forms, would like to be able to create a popup dialog to insert a newley defined type into the small table.
Not a disaster if auto increment cannot be done. It just means, during the early life of the db when I encounter undefined classes of main data, I will have to go directly into the small tables and manually add the new class, which will take longer than just entering a new name string into a popup prompt.