On Thu, Jul 23, 2009 at 12:39 PM, Shaun Seckman (Firaxis)<shaun.seck...@firaxis.com> 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