Re: [sqlite] Drop table error with existing views
Hello Graham ! Thank you so much ! With this pragma I can continue to solve my problem as before. Cheers ! On 1/4/19 12:42, Graham Holden wrote: PRAGMA legacy_alter_table=ON ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Drop table error with existing views
See https://www.sqlite.org/lang_altertable.html ... the ALTER TABLE command, by default, now alters references to it in triggers/views. To get the old behaviour, which I believe you need, use: PRAGMA legacy_alter_table=ON Graham Monday, April 01, 2019, 11:23:45 AM, Domingo Alvarez Duarte wrote: > Hello ! > I'm using sqlite3 for a long time and I used to be able to drop tables > that have views without error before but now it seems that it is not > possible anymore. > I mainly use it to restructure tables (add/move/remove fields) with > something like this: > > PRAGMA foreign_keys=OFF; > BEGIN; > CREATE TABLE "banks___new"( > id integer primary key, > account_id integer references ledger_accounts(id), > name varchar not null collate nocase unique, > new_field integer, -- for example here I'm adding a new > field > notes text collate nocase > ); > INSERT INTO "banks___new"( > "id", > "account_id", > "name", > "notes" > ) > SELECT > "id", > "account_id", > "name", > "notes" > FROM "banks"; > DROP TABLE "banks"; > ALTER TABLE "banks___new" RENAME TO "banks"; > PRAGMA foreign_key_check; > COMMIT; > PRAGMA foreign_keys=ON; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Drop table error with existing views
Hello ! I'm using sqlite3 for a long time and I used to be able to drop tables that have views without error before but now it seems that it is not possible anymore. I mainly use it to restructure tables (add/move/remove fields) with something like this: PRAGMA foreign_keys=OFF; BEGIN; CREATE TABLE "banks___new"( id integer primary key, account_id integer references ledger_accounts(id), name varchar not null collate nocase unique, new_field integer, -- for example here I'm adding a new field notes text collate nocase ); INSERT INTO "banks___new"( "id", "account_id", "name", "notes" ) SELECT "id", "account_id", "name", "notes" FROM "banks"; DROP TABLE "banks"; ALTER TABLE "banks___new" RENAME TO "banks"; PRAGMA foreign_key_check; COMMIT; PRAGMA foreign_keys=ON; As I said before it use to work in previous versions of sqlite3 but now it seems to not allow to drop tables that has views on then. How can something like the above be achieved now without need to drop and recreate the views (can be several views), any new pragma that I'm not aware off ? Cheers ! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
