On 12 Mar 2010, at 8:47am, Ben Harper wrote: > Under certain conditions, VIEWs do not have rowid values. > > Is there some recommended workaround that I can use to ensure that I always > get non-null rowids with every VIEW? > > CREATE TABLE one (i32 INTEGER); > INSERT INTO one VALUES(100); > INSERT INTO one VALUES(200); > CREATE VIEW v1 AS SELECT * FROM one; > CREATE VIEW v2 AS SELECT rowid,* FROM one; -- My current best guess
Good example. The posts I've seen here suggest that if you ever expect to reference the rowid number anywhere in your system you should explicitly declare it as a row in your table. By using the unofficially declared 'rowid' you are doing something clever but as you've seen it can come back and bite you. So ... CREATE TABLE one (rowid INTEGER PRIMARY KEY, i32 INTEGER); INSERT INTO one (i32) VALUES(100); makes the declaration explicit. Then if you ever use '*' it will pick up that column as well. If you have a table already declared with lots of data in it, and want to declare this column at this point, I'm not sure whether SQLite will do it without creating another column. You might be able to save some space by dumping the table to SQL commands, modifying the CREATE TABLE and INSERT instructions, then deleting and recreating the table using that file. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
