On 03/09/2017 09:39 PM, J. King wrote:
On 2017-03-09 09:35:00, "Dan Kennedy" <[email protected]> wrote:
On 03/09/2017 09:23 PM, J. King wrote:
I'm trying to write a minimal test case to produce an
SQLITE_MISMATCH response. The documentation suggests the following
should be sufficient:
CREATE TABLE test(id integer primary key) WITHOUT ROWID;
INSERT INTO test(id) VALUES('ook');
However, SQLite happily accepts the second statement. Is there some
trick to it that's not obvious?
Leave out the WITHOUT ROWID.
Dan.
Ah, exactly what I needed. Thanks!
Out of curiosity, can you provide some insight as to why it does not
produce a mismatch for a WITHOUT ROWID table?
The main b-tree in which data for a "rowid table" - any table that is
not a WITHOUT ROWID table or a virtual table - is stored uses 64-bit
integers for keys. If you declare a column "INTEGER PRIMARY KEY", it
becomes an alias for this underlying integer key. So in this case the
field must be set to an integer value - SQLite could not store it otherwise.
With a WITHOUT ROWID table, the main b-tree used to store data is more
flexible - it can have any type of key. So in this case there is no
restriction.
https://www.sqlite.org/fileformat.html#representation_of_sql_tables
and:
https://www.sqlite.org/rowidtable.html
https://www.sqlite.org/withoutrowid.html
Dan.
(apologies if my messages come out looking yucky; I'm still looking a
mail client that isn't awful dealing with mailing lists. :( )
--
J. King
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
