Re: [sqlite] How to determine if a column is autoincremented?
Thomas Briggs wrote: See http://www.sqlite.org/autoinc.html - INTEGER PRIMARY KEY will autoincrement only until you delete a row from the table. The above seems overstated. INTEGER PRIMARY KEYs with or without the AUTOINCREMENT keyword behave identically unless 1) the last row is deleted or 2) a row with the highest possible ROWID exists or has existed in the table, and an insertion is made. In case 1) the ROWID used for the new row will be one more than the current largest ROWID if the AUTOINCREMENT keyword is not present, and one more than the largest value ever used in the table if the keyword is present. In my mind both qualify as autoincrementing, with either being fine for most purposes, but the latter behavior can certainly be needed in some circumstances. In case 2) an error is thrown if AUTOINCREMENT is present; if not, a random ROWID is used if the row with the largest possible value is still in the table, otherwise one more than the largest current value. AUTOINCREMENT guarantees unique ROWID values for the life of the table; without it values can be reused, but will always be unique at any given time, and will autoincrement under normal circumstances. Gerry -- -- Gerry Snyder American Iris Society Director, Symposium Chair in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
RE: [sqlite] How to determine if a column is autoincremented?
See http://www.sqlite.org/autoinc.html - INTEGER PRIMARY KEY will autoincrement only until you delete a row from the table. -Tom > -Original Message- > From: Mario Gutierrez [mailto:[EMAIL PROTECTED] > Sent: Monday, October 24, 2005 11:05 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How to determine if a column is autoincremented? > > You learn something new everyday. I didn't know that was the > behaviour of > INTEGER PRIMARY KEY. All other databases I've worked with > require you to > explicitly declare an identiy/autoincrement column. I was > strictly looking > at the metadata returned by PRAGMA. SQLite is certainly a > different breed of > cat. > > Thanks for the tip. Anyway, if anyone out there uses > CodeSmith, I uploaded > the SQLite schema provider. > > -- > Mario Gutierrez > mario.l.gutierrez @ hotmail.com > > > > > >From: Peter Bierman <[EMAIL PROTECTED]> > >Reply-To: sqlite-users@sqlite.org > >To: sqlite-users@sqlite.org > >Subject: Re: [sqlite] How to determine if a column is > autoincremented? > >Date: Sun, 23 Oct 2005 23:20:19 -0700 > > > >Actually, on SQLite, 'INTEGER PRIMARY KEY' does designate a > special type of > >autoincremented column. > > > >The internal 64 bit rowid is used directly in that case, which is > >essentially 'free' storage. > > > >http://www.sqlite.org/faq.html#q1 > >http://www.sqlite.org/lang_createtable.html > >http://www.sqlite.org/autoinc.html > > > >-pmb > > > > > >At 12:19 PM -0700 10/22/05, Mario Gutierrez wrote: > >>Thanks for the reply. > >> > >>This would not work as I could define a table like this > >> > >>CRETE TABLE my_table ( > >> id INTEGER PRIMARY KEY, > >> ... > >>) > >> > >>This would meet your criteria, but 'id' is not an > autoincremented column. > >> > >>-- > >>Mario Gutierrez > >>mario.l.gutierrez @ hotmail.com > >> > >> > >> > >>>From: "David M. Cook" <[EMAIL PROTECTED]> > >>>Reply-To: sqlite-users@sqlite.org > >>>To: sqlite-users@sqlite.org > >>>Subject: Re: [sqlite] How to determine if a column is > autoincremented? > >>>Date: Sat, 22 Oct 2005 10:52:20 -0700 > >>> > >>>On Fri, Oct 21, 2005 at 05:38:28PM -0700, Mario Gutierrez wrote: > >>> > I'm writing a SQLite adapter for a code generation > tool. I'm a little > stumped on how you query SQLite to determine if a column is > autoincremented. I've tried > >>> > >>>An auto-increment column is defined as INTEGER PRIMARY > KEY, so look for > >>>primary key columns of type 'INTEGER'. > >>> > >>>Dave Cook > >> > >>_ > >>Express yourself instantly with MSN Messenger! Download > today - it's FREE! > >>http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ > > > > _ > Don't just search. Find. Check out the new MSN Search! > http://search.msn.click-url.com/go/onm00200636ave/direct/01/ > >
Re: [sqlite] How to determine if a column is autoincremented?
You learn something new everyday. I didn't know that was the behaviour of INTEGER PRIMARY KEY. All other databases I've worked with require you to explicitly declare an identiy/autoincrement column. I was strictly looking at the metadata returned by PRAGMA. SQLite is certainly a different breed of cat. Thanks for the tip. Anyway, if anyone out there uses CodeSmith, I uploaded the SQLite schema provider. -- Mario Gutierrez mario.l.gutierrez @ hotmail.com From: Peter Bierman <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to determine if a column is autoincremented? Date: Sun, 23 Oct 2005 23:20:19 -0700 Actually, on SQLite, 'INTEGER PRIMARY KEY' does designate a special type of autoincremented column. The internal 64 bit rowid is used directly in that case, which is essentially 'free' storage. http://www.sqlite.org/faq.html#q1 http://www.sqlite.org/lang_createtable.html http://www.sqlite.org/autoinc.html -pmb At 12:19 PM -0700 10/22/05, Mario Gutierrez wrote: Thanks for the reply. This would not work as I could define a table like this CRETE TABLE my_table ( id INTEGER PRIMARY KEY, ... ) This would meet your criteria, but 'id' is not an autoincremented column. -- Mario Gutierrez mario.l.gutierrez @ hotmail.com From: "David M. Cook" <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to determine if a column is autoincremented? Date: Sat, 22 Oct 2005 10:52:20 -0700 On Fri, Oct 21, 2005 at 05:38:28PM -0700, Mario Gutierrez wrote: I'm writing a SQLite adapter for a code generation tool. I'm a little stumped on how you query SQLite to determine if a column is autoincremented. I've tried An auto-increment column is defined as INTEGER PRIMARY KEY, so look for primary key columns of type 'INTEGER'. Dave Cook _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
Re: [sqlite] How to determine if a column is autoincremented?
On Sun, Oct 23, 2005 at 09:24:58PM -0700, David M. Cook wrote: > On Sat, Oct 22, 2005 at 12:19:04PM -0700, Mario Gutierrez wrote: > > > CRETE TABLE my_table ( > > id INTEGER PRIMARY KEY, > > ... > > ) > > > > This would meet your criteria, but 'id' is not an autoincremented column. > > Sorry, I don't get it. Why is it not? Also, why would one do that? If I > wanted an non-autoincremented primary key field I'd use INT PRIMARY KEY. Oops, I didn't realize there was an actual AUTOINCREMENT keyword that specifies a slightly different autoincrement behavior. http://www.sqlite.org/autoinc.html Dave Cook
Re: [sqlite] How to determine if a column is autoincremented?
Actually, on SQLite, 'INTEGER PRIMARY KEY' does designate a special type of autoincremented column. The internal 64 bit rowid is used directly in that case, which is essentially 'free' storage. http://www.sqlite.org/faq.html#q1 http://www.sqlite.org/lang_createtable.html http://www.sqlite.org/autoinc.html -pmb At 12:19 PM -0700 10/22/05, Mario Gutierrez wrote: Thanks for the reply. This would not work as I could define a table like this CRETE TABLE my_table ( id INTEGER PRIMARY KEY, ... ) This would meet your criteria, but 'id' is not an autoincremented column. -- Mario Gutierrez mario.l.gutierrez @ hotmail.com From: "David M. Cook" <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to determine if a column is autoincremented? Date: Sat, 22 Oct 2005 10:52:20 -0700 On Fri, Oct 21, 2005 at 05:38:28PM -0700, Mario Gutierrez wrote: I'm writing a SQLite adapter for a code generation tool. I'm a little stumped on how you query SQLite to determine if a column is autoincremented. I've tried An auto-increment column is defined as INTEGER PRIMARY KEY, so look for primary key columns of type 'INTEGER'. Dave Cook _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Re: [sqlite] How to determine if a column is autoincremented?
On Sat, Oct 22, 2005 at 12:19:04PM -0700, Mario Gutierrez wrote: > CRETE TABLE my_table ( > id INTEGER PRIMARY KEY, > ... > ) > > This would meet your criteria, but 'id' is not an autoincremented column. Sorry, I don't get it. Why is it not? Also, why would one do that? If I wanted an non-autoincremented primary key field I'd use INT PRIMARY KEY. Dave Cook
Re: [sqlite] How to determine if a column is autoincremented?
Thanks for the reply. This would not work as I could define a table like this CRETE TABLE my_table ( id INTEGER PRIMARY KEY, ... ) This would meet your criteria, but 'id' is not an autoincremented column. -- Mario Gutierrez mario.l.gutierrez @ hotmail.com From: "David M. Cook" <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to determine if a column is autoincremented? Date: Sat, 22 Oct 2005 10:52:20 -0700 On Fri, Oct 21, 2005 at 05:38:28PM -0700, Mario Gutierrez wrote: > I'm writing a SQLite adapter for a code generation tool. I'm a little > stumped on how you query SQLite to determine if a column is > autoincremented. I've tried An auto-increment column is defined as INTEGER PRIMARY KEY, so look for primary key columns of type 'INTEGER'. Dave Cook _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Re: [sqlite] How to determine if a column is autoincremented?
Thanks for the reply. Your approach determines if 'my_table' has a column that is autoincremented. I needed a way to determine if a specific column is autoincremented. I resorted to a regular expression match against the SQL to check if a specific column is autoincremented. -- Mario Gutierrez mario.l.gutierrez @ hotmail.com From: Kurt Welgehausen <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to determine if a column is autoincremented? Date: Sat, 22 Oct 2005 12:49:30 -0500 select sql from sqlite_master where type='table' and tbl_name='my_table' and sql like '%autoincrement%' OR select sql like '%autoincrement%' from (select sql from sqlite_master where type='table' and tbl_name='my_table') Regards _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Re: [sqlite] How to determine if a column is autoincremented?
On Fri, Oct 21, 2005 at 05:38:28PM -0700, Mario Gutierrez wrote: > I'm writing a SQLite adapter for a code generation tool. I'm a little > stumped on how you query SQLite to determine if a column is > autoincremented. I've tried An auto-increment column is defined as INTEGER PRIMARY KEY, so look for primary key columns of type 'INTEGER'. Dave Cook
Re: [sqlite] How to determine if a column is autoincremented?
select sql from sqlite_master where type='table' and tbl_name='my_table' and sql like '%autoincrement%' OR select sql like '%autoincrement%' from (select sql from sqlite_master where type='table' and tbl_name='my_table') Regards
