Thanks a lot!
It really helped!
---------------------------------------------------- Christian Kienle http://www.QtForum.org ----------------------------------------------------
On Nov 21, 2004, at 6:52 PM, [EMAIL PROTECTED] wrote:
Christian Kienle <[EMAIL PROTECTED]> writes:
I am using SQLite 2.8.5
Firstly, 2.8.5 is ancient. Numerous bugs have been fixed between 2.8.5 and
the current version in the 2.8 branch: 2.8.15. You really should upgrade.
and I wanna use a primary key field in one of my tables.
~$ sqlite :memory: SQLite version 2.8.5 Enter ".help" for instructions sqlite> create table testing(title text, id AUTOINCREMENT); sqlite> insert into testing(title) VALUES('this is just a test'); sqlite> select * from testing; this is just a test| sqlite>
Although you state that you want to use a primary key field, you're not
specifying anything to be a primary key. I *think* you're looking for 'id' to
be your primary key. A couple of points here:
- you need to specify that id is a primary key
- the attribute AUTOINCREMENT is not implemented in 2.8.5. I think that's
only in the 3.0 tree. You can get the behavior you probably want, though.
CREATE TABLE testing(id INTEGER PRIMARY KEY, title TEXT);
By creating your table this way, with 'id' being an INTEGER PRIMARY KEY, 'id'
will always be assigned an integer value one greater than the previously
highest value of 'id' in the table (starting at 1), if you either don't
provide an 'id' value, or if you provide it as NULL:
This one doesn't provide 'id' at all:
INSERT INTO testing(title) VALUES ('this is test 1');
This one provides 'id' explicitly as NULL:
INSERT INTO testing(id, title) VALUES (null, 'this test 2');
If you specify a value for 'id', it will take on that value:
INSERT INTO testing(id, title) VALUES (23, 'this is test 23');
A primary key must be unique, so if you try to insert a record with an 'id'
value that already exists in the table, you'll get an error:
INSERT INTO testing(id, title) VALUES (23, 'this is another test 23');
If you really want to replace the row with an existing 'id' value, use INSERT OR REPLACE instead of INSERT:
INSERT OR REPLACE INTO testing(id, title) VALUES (23, 'this is yet another test 23');
Here's a transcript of the above so you can see what's happening:
sqlite> .mode line sqlite> CREATE TABLE testing(id INTEGER PRIMARY KEY, title TEXT); sqlite> .schema CREATE TABLE testing(id INTEGER PRIMARY KEY, title TEXT); sqlite> INSERT INTO testing(title) VALUES ('this is test 1'); sqlite> INSERT INTO testing(id, title) VALUES (null, 'this test 2'); sqlite> INSERT INTO testing(id, title) VALUES (23, 'this is test 23'); sqlite> select * from testing; id = 1 title = this is test 1
id = 2 title = this test 2
id = 23
title = this is test 23
sqlite> INSERT INTO testing(id, title) VALUES (23, 'this is another test 23');
SQL error: constraint failed
sqlite> INSERT OR REPLACE INTO testing(id, title)
...> VALUES (23, 'this is yet another test 23');
sqlite> select * from testing;
id = 1
title = this is test 1
id = 2 title = this test 2
id = 23 title = this is yet another test 23 sqlite>
