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;
pragma recursive_triggers = no;
pragma ignore_check_constraints = yes;
but that doesn't seem to help this situation.
Thanks,
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
