RE: [sqlite] Resetting primary key auto-increment after table re-create
Sorry...That's what I meant - scary you can insert into a table that has this property without first setting The propert off without re-seeding - ie. How Sybase and SqlServer handle this.. It would be good if this was documented a little clearer Thx all for your assistance - onward and upward (so to speak) Cheers... > -Original Message- > From: Kurt Welgehausen [mailto:[EMAIL PROTECTED] > Sent: Tuesday, 9 March 2004 2:34 AM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Resetting primary key auto-increment > after table re-create > > > > So what you are saying is that if I recreate the table I can insert > > all the old values back into the newly created table (including the > > primary key values) and the primary key column wont > complain? That's a > > bit scary! > > It's not scary, it's essential. In a real database, an > integer primary key will almost always be referenced by one > or more foreign keys. Changing the primary key would destroy > referential integrity. > > Regards > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Resetting primary key auto-increment after table re-create
- Forwarded by Ben Carlyle/AU/IRSA/Rail on 09/03/2004 10:56 AM -
Ben Carlyle
09/03/2004 10:54 AM
To: "Roger Hawkins" <[EMAIL PROTECTED]>@CORP
cc:
Subject: RE: [sqlite] Resetting primary key auto-increment after table
re-create
Roger,
"Roger Hawkins" <[EMAIL PROTECTED]>
08/03/2004 03:18 PM
To: <[EMAIL PROTECTED]>
cc:
Subject: RE: [sqlite] Resetting primary key auto-increment after table
re-create
> Thanks for your reply...
> I havent found any reference to this in the doco so Im not sure how any
> of this might work..
http://sqlite.org/faq.html#q1
Pay particular attention to the "is logically equivalent" part.
> So what you are saying is that if I recreate the table I can insert all
> the old values back into the newly created table (including the primary
> key values) and the primary key column wont complain? That's a bit
> scary!
So long as you don't insert the same number into the INTEGER PRIMARY KEY
twice, sqlite will not complain. It's a primarily a primary key. It only
doubles as an auto-increment field when you insert NULLs into it.
> Even if this is true what happens when I next insert a value (normally
> without specifying the primary key field) - does it just pick up the
> latest?
> Any one tried this?
Yes. You can, too:
CREATE TABLE foo(bar INTEGER PRIMARY KEY);
INSERT INTO foo VALUES(NULL);
INSERT INTO foo VALUES(2);
INSERT INTO foo VALUES(NULL);
INSERT INTO foo VALUES(3); -- SQL Error: PRIMARY KEY must be unique
SELECT * FROM foo:
1
2
3
Benjamin.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Resetting primary key auto-increment after table re-create
Roger Hawkins wrote: So what you are saying is that if I recreate the table I can insert all the old values back into the newly created table (including the primary key values) and the primary key column wont complain? That's a bit scary! I think if you specify a value it will use it and if you don't, then a value will be generated for you automatically. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Resetting primary key auto-increment after table re-create
"Roger Hawkins" <[EMAIL PROTECTED]> writes: > Thanks for your reply... > I havent found any reference to this in the doco so Im not sure how any > of this might work.. > > So what you are saying is that if I recreate the table I can insert all > the old values back into the newly created table (including the primary > key values) and the primary key column wont complain? That's a bit > scary! > > Even if this is true what happens when I next insert a value (normally > without specifying the primary key field) - does it just pick up the > latest? Try the ".dump" command from the sqlite shell. It generates sql commands that you could save and recreate the database. Each of your primary keys which were generated automagically will be shown, and if you take that output and pipe it to a new sqlite shell, it will recreate an identical copy of your database. I believe that the next automagically generated (auto-increment) primary key used if you provide NULL in a new INSERT, will be one greater than the maximum value currently in the column. That'd be an easy experiment to try. Derrell - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Resetting primary key auto-increment after table re-create
Thanks for your reply... I havent found any reference to this in the doco so Im not sure how any of this might work.. So what you are saying is that if I recreate the table I can insert all the old values back into the newly created table (including the primary key values) and the primary key column wont complain? That's a bit scary! Even if this is true what happens when I next insert a value (normally without specifying the primary key field) - does it just pick up the latest? I guess I will have to try this but it all sounds a little confusing... Any one tried this? > -Original Message- > From: Will Leshner [mailto:[EMAIL PROTECTED] > Sent: Monday, 8 March 2004 3:47 PM > To: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> > Subject: Re: [sqlite] Resetting primary key auto-increment > after table re-create > > > > On Mar 7, 2004, at 8:51 PM, Roger Hawkins wrote: > > > turning off of auto-incremement > > then I do my insert of old table rows into new table > > and then turning back on auto-increment with a seed value (starting > > point) > > would be the way to go. > > > > If you specify a value for the primary key when you do the > insert, then > that is the value that will be used (assuming it is unique). > Won't that > work? > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Resetting primary key auto-increment after table re-create
On Mar 7, 2004, at 8:51 PM, Roger Hawkins wrote: turning off of auto-incremement then I do my insert of old table rows into new table and then turning back on auto-increment with a seed value (starting point) would be the way to go. If you specify a value for the primary key when you do the insert, then that is the value that will be used (assuming it is unique). Won't that work? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
