J Decker wrote: > On Sun, Oct 26, 2014 at 10:32 AM, Clemens Ladisch wrote: >> Charles Samuels wrote: >>> it was my understanding that alter table added the extra column "elsewhere". >> >> It adds the extra column "nowhere". When SQLite reads a table row has >> fewer columns than in the CREATE TABLE statement, the remaining columns' >> values are assumed to have their default values. > > That is interesting; so if I alter the default values, all rows that > existed before the column added and the default change get the changed > value?
The ALTER TABLE command cannot change the default values, so this cannot happen. ... well, "cannot" is relative: $ sqlite3 test.db sqlite> CREATE TABLE t(x); sqlite> INSERT INTO t(x) VALUES (1); sqlite> ALTER TABLE t ADD y DEFAULT 42; sqlite> INSERT INTO t(x) VALUES (2); sqlite> SELECT * FROM t; 1|42 2|42 sqlite> PRAGMA writable_schema = on; sqlite> UPDATE sqlite_master SET sql = 'CREATE TABLE t(x, y DEFAULT 666)' WHERE name = 't' AND type = 'table'; sqlite> ^D $ sqlite3 test.db sqlite> SELECT * FROM t; 1|666 2|42 (The documentation of PRAGMA writable_schema says: "Warning: misuse of this pragma can easily result in a corrupt database file." Well, there you have your corruption.) Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
