(I apologize if this has been raised before; I see [1] which is vaguely related but not the same, and nothing on the ticket tracker or such. As an aside, it would be nice to have the links to the public offsite archives of sqlite-users replicated on the Mailman page for the list itself, since that's normally where I would expect to find information related to a specific mailing list.)
[1] http://thread.gmane.org/gmane.comp.db.sqlite.general/10150 It seems that in SQLite 3.7.13 (and probably earlier versions), for a column that is an integer primary key (and therefore a rowid alias), default values are ignored, whereas a merely unique integer column that does not alias the rowid handles defaults like other columns. This bit me while I was trying to define a table constrained to hold only one row, for the purposes of storing database-wide parameters and metadata. Here's the use case that bit me, simplified: CREATE TABLE foo_master ( zero INTEGER NOT NULL PRIMARY KEY DEFAULT 0 CHECK (zero = 0), foo BLOB NULL DEFAULT NULL ); Subsequently doing an INSERT INTO foo_master DEFAULT VALUES fails with "constraint failed". Creating a similar table with CREATE TABLE fudge (x INTEGER NOT NULL PRIMARY KEY DEFAULT 500); without the CHECK constraint, and then doing an INSERT ... DEFAULT VALUES, yields the row (1) rather than (500). Changing PRIMARY KEY to UNIQUE or PRIMARY KEY DESC (the latter of which, per the CREATE TABLE documentation, does not create a rowid alias column) causes 500 to be inserted instead. I've placed the output of EXPLAIN INSERT ... DEFAULT VALUES in two cases below for reference (slightly reformatted). It is not _entirely_ unreasonable to imagine that default values on unique columns in general would result in strange behavior, since they are rarely useful, but I figured I would post this in case someone else runs into similar issues. Since it is easy to work around this by avoiding the rowid alias, I will simply do that in my application, but feedback on whether this was a conscious design choice in SQLite, whether it is documented somewhere that I have missed, and/or whether it is simply a bug or undefined behavior would be appreciated. ---> Drake Wilson After CREATE TABLE fudge (x INTEGER NOT NULL PRIMARY KEY DEFAULT 500), running EXPLAIN INSERT INTO fudge DEFAULT VALUES produces: addr opcode p1 p2 p3 p4 p5 comment 0 Trace 0 0 0 00 1 Goto 0 9 0 00 2 OpenWrite 0 2 0 1 00 3 NewRowid 0 1 0 00 4 Null 0 2 0 00 5 MakeRecord 2 1 3 d 00 6 Insert 0 3 1 fudge 1b 7 Close 0 0 0 00 8 Halt 0 0 0 00 9 Transaction 0 1 0 00 10 VerifyCookie 0 15 0 00 11 TableLock 0 2 1 fudge 00 12 Goto 0 2 0 00 After CREATE TABLE fudge (x INTEGER NOT NULL UNIQUE DEFAULT 500), running EXPLAIN INSERT INTO fudge DEFAULT VALUES produces: addr opcode p1 p2 p3 p4 p5 comment 0 Trace 0 0 0 00 1 Goto 0 19 0 00 2 OpenWrite 0 2 0 1 00 3 OpenWrite 1 4 0 keyinfo(1,BINARY) 00 4 NewRowid 0 2 0 00 5 Integer 500 3 0 00 6 HaltIfNull 19 2 3 fudge.x may not be NULL 00 7 SCopy 3 4 0 00 8 SCopy 2 5 0 00 9 MakeRecord 4 2 1 dd 00 10 SCopy 2 6 0 00 11 IsUnique 1 13 6 4 00 12 Halt 19 2 0 column x is not unique 00 13 IdxInsert 1 1 0 10 14 MakeRecord 3 1 6 d 00 15 Insert 0 6 2 fudge 1b 16 Close 0 0 0 00 17 Close 1 0 0 00 18 Halt 0 0 0 00 19 Transaction 0 1 0 00 20 VerifyCookie 0 17 0 00 21 TableLock 0 2 1 fudge 00 22 Goto 0 2 0 00 Run using SQLite 3.7.13 2012-06-11 02:05:22, from Debian sid on AMD64, packages sqlite3 and libsqlite3-0 both version 3.7.13-1. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
