Sorry for replying so late...
Looking in the list archive of sqlite-us...@sqlite.org, I found the reason for
my observed bug:
From http://www.sqlite.org/lang_createtable.html:
"According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not the case in
SQLite. SQLite allows NULL values in a PRIMARY KEY column. We could change
SQLite to conform to the standard (and we might do so in the future), but by the
time the oversight was discovered, SQLite was in such wide use that we feared
breaking legacy code if we fixed the problem. So for now we have chosen to
continue allowing NULLs in PRIMARY KEY columns. Developers should be aware,
however, that we may change SQLite to conform to the SQL standard in future and
should design new programs accordingly."
The problem is independend from the used datatype, INTEGER or CHAR or something
else.
The solution would be to add an extra NOT NULL to the PRIMARY KEY constraint,
like this:
CREATE TABLE TestTable (
a VARCHAR(4) PRIMARY KEY NOT NULL,
b VARCHAR(4) NOT NULL,
c VARCHAR(4)
)
Not nice, but it is a way...
Greetings
Robert
-------
Mark Lawrence schrieb:
On Mon Oct 12, 2009 at 02:54:38PM +0200, Robert Roggenbuck wrote:
I detected that it is possible to enter NULL in a table-column defined as
PRIMARY KEY. Is it a bug or a feature of the "manifest typing" of SQLite?
Or is there something in DBD::SQLite going wrong?
Most likely a 'feature' of SQLite. Running your code manually:
sqlite> CREATE TABLE TestTable (
...> a INTEGER PRIMARY KEY,
...> b INTEGER NOT NULL,
...> c INTEGER
...> );
sqlite> INSERT INTO TestTable VALUES (NULL, 1, NULL);
sqlite> select * from TestTable;
1 1 NULL
Reading http://www.sqlite.org/autoinc.html it appears that SQLite has
an automatic 'autoincrement' feature. Why it converts NULL into auto is
beyond me. I would try perhaps try the same question at:
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Cheers,
Mark.
