On 01/07/2011 09:19 PM, BareFeetWare wrote: > Hi All, > > I have developed some software that allows the user to change the schema of > tables. It does this by: > > 1. Backs up the rows from the old table into a temporary duplicate table > 2. Drops the old table > 3. Creates the new table > 4. Inserts the rows from the duplicate into the new table > > The SQL looks like this: > > begin immediate; > create temporary table "Backup" as select * from "My Table"; > drop table "My Table"; > create table "My Table" ( new definition here ); > insert into "My Table" select * from "Backup"; > drop table "Backup"; > commit; > > It all works great. But I've hit a problem if another table has a foreign key > to this table that cascades deletes. For example, if I have another table > like this: > > create table "Child Table" > ( ID integer > primary key > references "My Table"(ID) > on delete cascade > , "Other Columns" > ) > > Then my rebuilding of "My Table" deletes all the rows in "Child Table". > > Is there a way to temporarily turn off cascading changes? I've tried > prefixing my transaction (above) with: > > pragma foreign_keys = no;
The command above should have disabled all foreign-key constraint checks (including cascades etc.). Check it for typos perhaps. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
