How to determine if a column is autoincremented?

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

How to determine if a column is autoincremented?

Mario Gutierrez
Hi all,

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

        PRAGMA table_info('my_table');

which returns all the columns for the table including a flag to tell if the
column is a primary key member. However, there is not a flag to indicate
whether a column is autoincremented. I then tried

        SELECT * FROM sqlite_sequence;

which doesn't return anything if the table is newly created. I'm not
interfacing with the DLL directly, so I hope it can be done with a query.

--
Mario Gutierrez
mario.l.gutierrez @ hotmail.com

_________________________________________________________________
Don?t just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/

Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

Kurt Welgehausen
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
Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

David M. Cook
In reply to this post by Mario Gutierrez
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
Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

Mario Gutierrez
In reply to this post by Kurt Welgehausen
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 <[hidden email]>
>Reply-To: [hidden email]
>To: [hidden email]
>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/

Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

Mario Gutierrez
In reply to this post by David M. Cook
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" <[hidden email]>
>Reply-To: [hidden email]
>To: [hidden email]
>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/

Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

David M. Cook
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
Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

Peter Bierman
In reply to this post by Mario Gutierrez
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" <[hidden email]>
>>Reply-To: [hidden email]
>>To: [hidden email]
>>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/

Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

David M. Cook
In reply to this post by David M. Cook
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
Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

David M. Cook
In reply to this post by Mario Gutierrez
On Sat, Oct 22, 2005 at 12:19:04PM -0700, 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.

OK, now I think I get it: you're using an explicit AUTOINCREMENT keyword

INTEGER PRIMARY KEY AUTOINCREMENT

Since you can't have more than one PRIMARY KEY per table, you can combine
Kurt and my suggestions: look for primary key columns of type 'INTEGER' and
check for AUTOINCREMENT in the table definition (it can't appear without the
INTEGER PRIMARY KEY part).  However, it's an autoincrement column whether
the AUTOINCREMENT keyword is present or not.

But certainly having this returned from the table_info PRAGMA would be
nicer.

Dave Cook

Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

Mario Gutierrez
In reply to this post by Peter Bierman
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 <[hidden email]>
>Reply-To: [hidden email]
>To: [hidden email]
>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" <[hidden email]>
>>>Reply-To: [hidden email]
>>>To: [hidden email]
>>>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/

Reply | Threaded
Open this post in threaded view
|

RE: How to determine if a column is autoincremented?

Thomas Briggs
In reply to this post by Mario Gutierrez

   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:[hidden email]]
> Sent: Monday, October 24, 2005 11:05 PM
> To: [hidden email]
> 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 <[hidden email]>
> >Reply-To: [hidden email]
> >To: [hidden email]
> >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" <[hidden email]>
> >>>Reply-To: [hidden email]
> >>>To: [hidden email]
> >>>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/
>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to determine if a column is autoincremented?

Gerry Snyder
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