[Default] On Sat, 27 Oct 2007 16:26:36 +0200, "Michael Ruck"
<[EMAIL PROTECTED]> wrote:
>Hi,
>
>I have a table of unique values in the following format:
>
>CREATE TABLE categories (id INTEGER PRIMARY KEY, category UNIQUE TEXT)
>
>I want inserts into this table to succeed, even though the corresponding
>entry already exists. So I use inserts in the following format:
>
>INSERT OR IGNORE INTO categories VALUES (NULL, ?)
>
>However, if I follow this successful execution with a call to
>sqlite3_last_insert_rowid() I don't get the rowid of row, which caused the
>insert to be ignored, but one I preformed previously (which doesn't
>necessarily have anything to do with this one.) This causes some relations
>in my database model to break.
>
>I know I could use INSERT OR FAIL and a subsequent SELECT, but that seems
>awkward and like unnecessary code bloat to me. Additionally I kind of think,
>
>this breaks the description and sense of sqlite3_last_insert_rowid().
>
>SQlite version used is 3.3.16.
>
>Is this intentional? Any suggestions or should I file a ticket for this?
>
>Thanks!
>Mike
You supply NULL for the primary key, which in this case means
SQLite will make up a new id for you.
http://www.sqlite.org/lang_createtable.html :
Specifying a PRIMARY KEY normally just creates a UNIQUE index on
the corresponding columns. However, if primary key is on a
single column that has datatype INTEGER, then that column is
used internally as the actual key of the B-Tree for the table.
This means that the column may only hold unique integer values.
(Except for this one case, SQLite ignores the datatype
specification of columns and allows any kind of data to be put
in a column regardless of its declared datatype.) If a table
does not have an INTEGER PRIMARY KEY column, then the B-Tree key
will be a automatically generated integer. The B-Tree key for a
row can always be accessed using one of the special names
"ROWID", "OID", or "_ROWID_". This is true regardless of whether
or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY
column can also include the keyword AUTOINCREMENT. The
AUTOINCREMENT keyword modified the way that B-Tree keys are
automatically generated. Additional detail on automatic B-Tree
key generation is available separately.
http://www.sqlite.org/autoinc.html :
When a new row is inserted into an SQLite table, the ROWID can
either be specified as part of the INSERT statement or it can be
assigned automatically by the database engine. To specify a
ROWID manually, just include it in the list of values to be
inserted. For example:
CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
If no ROWID is specified on the insert, an appropriate ROWID is
created automatically. The usual algorithm is to give the newly
created row a ROWID that is one larger than the largest ROWID in
the table prior to the insert.
And:
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then
a slightly different ROWID selection algorithm is used.
By supplying NULL as the key (ROWID) you actually don't specify
a value, so SQLite creates a new row with a new id.
If you want category to be unique, you will have to specify a
UNIQUE constraint for it.
HTH
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
