Hi Wayne, What I've decided to do is manage it myself. I have a trigger on deletes to the main table, which stores the deleted primary key in another table. If the other table is empty, I query for the max key in the main table and increment it by one. If the other table is not empty, I take a value from that table and use it, then delete that value from that table.
The values of the primary keys don't matter to me so long as they are within my range, so I'm just trying to re-use any "holes" in the sequence just in case I ever run out (which I imagine won't happen in my lifetime but hey you never know). Thanks for the replies, Brett -----Original Message----- From: w b [mailto:[EMAIL PROTECTED] Sent: Monday, January 15, 2007 11:42 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Restricting integer primary key values Hi Brett, I dont think that there is a way within Sqlite to automagically do what you are looking for. I know that within Oracle they have the ability for you to define a sequence which allows lower and upper bounds to be defined as well as if the sequence can loop, which sounds like what you could be looking for. However typically even within Oracle you then use the value obtained from the sequence value into your insert statement. This however is not automatic and would still require you to check the bounds conditions, since a declared sequence can be used for anything you really like and is not tied to a specific primary key within a specific table. In the case of sqlite you would probably need to create a C function to do that your self. Dr H, I was wondering if it would be worth considering exposing the ability for a user to define a function that could override the auto increment abilities of the primary key, or would that open up a can of worms ? Wayne Brett Keating <[EMAIL PROTECTED]> wrote: Hi, This does indeed work, but only to prevent primary keys from being inserted into the database if they fall out of range. What I was hoping was that the with this command, the sqlite3 would always automatically choose primary keys that fell within this range. However with this command, the sqlite3 will eventually automatically choose a primary key outside of this range (I tested with a range of 100 to 1000), and fail to insert the new item based on the check clause. I tried to repeat the statement hoping that a new primary key would be generated and eventually the insertion would succeed, but this ended up causing an infinite loop (I think the key once chosen by the automated algorithm remains the same for repeated attempts). Are there any alternatives? I want automatic key generation that is restricted to a range, and I don't want failures unless all possible values are taken. I will continue trying other things in the meantime. Thanks, Brett -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, January 14, 2007 6:09 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Restricting integer primary key values "Brett Keating" wrote: > Hi, > > I don't want to spam the list but basically, if "id INTEGER PRIMARY > KEY CHECK (id > 0 and id < 0xFFFFFFFF)" or something along those lines > will work, please let me know. It's not clear if sqlite3 supports > checks on primary keys from what I can tell. > SQLite does not support hexadecimal constants. Use 4294967295 instead of 0xFFFFFFFF and it should work. -- D. Richard Hipp ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
