Re: [sqlite] Defining a table that starts rowid as 0

[John Machin]

On 24/07/2009 3:10 PM, Simon Slavin wrote:
> On 24 Jul 2009, at 5:49am, John Machin wrote:
> 
>> On 24/07/2009 3:22 AM, Simon Slavin wrote:
>>
>>> And note that if you have a column which is an integer that
>>> has doesn't allow duplicates, SQLite will automatically use that
>>> column as the one it uses for _rowid_, etc..  So define your own
>>> integer column, feed it whatever integers you want, and you won't
>>> waste any space.
>> Documentation reference, please.
> 
> I'm sorry.  I think that instead of 'integer unique' what I meant was  
> 'autoincrement'.  It's in the section of the documentation about row  
> ids.  I can't get at the web right now.

Perhaps not:

sqlite> create table bar(x text, y integer autoincrement);
SQL error: near "autoincrement": syntax error
sqlite> create table bar(x text, y autoincrement);
SQL error: near "autoincrement": syntax error

In any case 'autoincrement' doesn't correlate with "feed it whatever 
integers you want".

Perhaps you meant 'integer primary key':

sqlite> create table bar(x text, y integer primary key);
sqlite> insert into bar values('aaa', 1);
sqlite> insert into bar values('bbb', 42);
sqlite> insert into bar values('ccc', 666);
sqlite> select rowid, * from bar;
1|aaa|1
42|bbb|42
666|ccc|666
sqlite>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Simon Slavin]

On 24 Jul 2009, at 5:49am, John Machin wrote:

> On 24/07/2009 3:22 AM, Simon Slavin wrote:
>
>> And note that if you have a column which is an integer that
>> has doesn't allow duplicates, SQLite will automatically use that
>> column as the one it uses for _rowid_, etc..  So define your own
>> integer column, feed it whatever integers you want, and you won't
>> waste any space.
>
> Documentation reference, please.

I'm sorry.  I think that instead of 'integer unique' what I meant was  
'autoincrement'.  It's in the section of the documentation about row  
ids.  I can't get at the web right now.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[John Machin]

On 24/07/2009 3:22 AM, Simon Slavin wrote:

> And note that if you have a column which is an integer that  
> has doesn't allow duplicates, SQLite will automatically use that  
> column as the one it uses for _rowid_, etc..  So define your own  
> integer column, feed it whatever integers you want, and you won't  
> waste any space.

Documentation reference, please.

How do you account for this:

SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table bar(x text, y integer unique);
sqlite> insert into bar values('aaa', 1);
sqlite> insert into bar values('bbb', 42);
sqlite> insert into bar values('ccc', 666);
sqlite> select rowid, * from bar;
1|aaa|1
2|bbb|42
3|ccc|666
sqlite> insert into bar values('ddd', 42);
SQL error: column y is not unique
sqlite>

?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[P Kishor]

On Thu, Jul 23, 2009 at 12:39 PM, Shaun Seckman
(Firaxis) wrote:
> I currently have all my tables with a column called "ID" that is defined
> as Integer Primary Key. For legacy code purposes, I need ID to start at
> 0 and not at 1 however I'd like to have this be defined as part of the
> table schema and not as part of the insertion statement.  I tried
> defining it as "integer primary key default 0" but that didn't work.
> Any other ideas?

INSERT the first row explicitly, and it will work thereafter

sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, bar TEXT);
sqlite> INSERT INTO foo (id, bar) VALUES (0, 'test');
sqlite> INSERT INTO foo (bar) VALUES ('more');
sqlite> SELECT * FROM foo;
0|test
1|more
sqlite> SELECT rowid, * FROM foo;
0|0|test
1|1|more
sqlite>

>
> -Shaun
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
> Sent: Thursday, July 23, 2009 1:31 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Defining a table that starts rowid as 0
>
> On Thu, Jul 23, 2009 at 06:22:53PM +0100, Simon Slavin scratched on the
> wall:
>>
>> On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:
>>
>> >   Using rowid for anything is not a good idea. There's no guarantee
>
>> > that the
>> > column values associated with each rowid are static. It's much
>> > better to
>> > ignore the rowid and use either a natural primary key or a defined
>> > one.
>>
>> Agreed.  And note that if you have a column which is an integer that
>> has doesn't allow duplicates,
>
>  The column has to very specifically be defined "INTEGER PRIMARY KEY".
>  "INT UNIQUE" won't cover it.
>
>  http://sqlite.org/lang_createtable.html#rowid
>
>  -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Shaun Seckman (Firaxis)]

I currently have all my tables with a column called "ID" that is defined
as Integer Primary Key. For legacy code purposes, I need ID to start at
0 and not at 1 however I'd like to have this be defined as part of the
table schema and not as part of the insertion statement.  I tried
defining it as "integer primary key default 0" but that didn't work.
Any other ideas?

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: Thursday, July 23, 2009 1:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Defining a table that starts rowid as 0

On Thu, Jul 23, 2009 at 06:22:53PM +0100, Simon Slavin scratched on the
wall:
> 
> On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:
> 
> >   Using rowid for anything is not a good idea. There's no guarantee

> > that the
> > column values associated with each rowid are static. It's much  
> > better to
> > ignore the rowid and use either a natural primary key or a defined  
> > one.
> 
> Agreed.  And note that if you have a column which is an integer that  
> has doesn't allow duplicates, 

  The column has to very specifically be defined "INTEGER PRIMARY KEY". 
  "INT UNIQUE" won't cover it.

  http://sqlite.org/lang_createtable.html#rowid

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Jay A. Kreibich]

On Thu, Jul 23, 2009 at 06:22:53PM +0100, Simon Slavin scratched on the wall:
> 
> On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:
> 
> >   Using rowid for anything is not a good idea. There's no guarantee  
> > that the
> > column values associated with each rowid are static. It's much  
> > better to
> > ignore the rowid and use either a natural primary key or a defined  
> > one.
> 
> Agreed.  And note that if you have a column which is an integer that  
> has doesn't allow duplicates, 

  The column has to very specifically be defined "INTEGER PRIMARY KEY". 
  "INT UNIQUE" won't cover it.

  http://sqlite.org/lang_createtable.html#rowid

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Simon Slavin]

On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:

>   Using rowid for anything is not a good idea. There's no guarantee  
> that the
> column values associated with each rowid are static. It's much  
> better to
> ignore the rowid and use either a natural primary key or a defined  
> one.

Agreed.  And note that if you have a column which is an integer that  
has doesn't allow duplicates, SQLite will automatically use that  
column as the one it uses for _rowid_, etc..  So define your own  
integer column, feed it whatever integers you want, and you won't  
waste any space.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Rich Shepard]

On Thu, 23 Jul 2009, Shaun Seckman (Firaxis) wrote:

>Is it possible in the create a table where the rowid
> will start at 0 instead of 1 prior to inserting a row and explicitly
> stating that the rowid is 0?

Shaun,

   Using rowid for anything is not a good idea. There's no guarantee that the
column values associated with each rowid are static. It's much better to
ignore the rowid and use either a natural primary key or a defined one.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Igor Tandetnik]

Shaun Seckman (Firaxis)
 wrote:
>Is it possible in the create a table where the rowid
> will start at 0 instead of 1 prior to inserting a row and explicitly
> stating that the rowid is 0?

You can specify rowid explicitly in an insert statement: insert into 
mytable(rowid, ...) values (0, ...)

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Defining a table that starts rowid as 0

[Shaun Seckman (Firaxis)]

Hello,

Is it possible in the create a table where the rowid
will start at 0 instead of 1 prior to inserting a row and explicitly
stating that the rowid is 0?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users