On Thu, Jul 23, 2009 at 12:39 PM, Shaun Seckman
(Firaxis)<[email protected]> 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: [email protected]
> [mailto:[email protected]] 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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
