Re: [firebird-support] Temporary disable FK constraint
On 06/11/15 19:21, Helen Borrie hele...@iinet.net.au [firebird-support] wrote: > Still, I'm bemused as to why you want to fiddle with the constraints > if you're not changing them. Well, to give some rough data; with constraints, my test case takes 10 mins to import, without, 4 mins (that includes the time to drop/recreate the constraints) +50% speed-up, multiplied by a bigger dataset, multiplied by all the deployed DB instances that'll need this update. Worth the effort. Scott
Re: [firebird-support] Temporary disable FK constraint
Hello Scott, Saturday, November 7, 2015, 4:01:48 AM, you wrote: > I have a feeling that the answer is 'no', but... > In a FB2.5 system is there a way to temporarily disable foreign key > constraints? i.e. something like 'ALTER INDEX ... INACTIVE' Constraints and their indexes, no. A constraint isn't just an index, it involves system-created triggers as well. > Got a big lump of clean data I want to drop into a table. > Alternatively, how safe is dropping/recreating the FKs on the fly? If "on the fly" means "while users are logged in" that's not safe. Otherwise it's safe - as long as you remember to reinstate and test the constraints before allowing user logins. Still, I'm bemused as to why you want to fiddle with the constraints if you're not changing them. Helen
[firebird-support] Temporary disable FK constraint
I have a feeling that the answer is 'no', but... In a FB2.5 system is there a way to temporarily disable foreign key constraints? i.e. something like 'ALTER INDEX ... INACTIVE' Got a big lump of clean data I want to drop into a table. Alternatively, how safe is dropping/recreating the FKs on the fly? Scott
Re: [firebird-support] Temporary disable FK constraint
Scott Morgan bl...@blueyonder.co.uk [firebird-support] wrote: > In a FB2.5 system is there a way to temporarily disable foreign key > constraints? i.e. something like 'ALTER INDEX ... INACTIVE' Well, here is the way to activate indexes and foreign keys: How to activate all indexes? http://www.firebirdfaq.org/faq274/ So doing the reverse should work too: SET TERM !! ; EXECUTE BLOCK AS DECLARE VARIABLE stmt VARCHAR(1000); BEGIN for select 'ALTER INDEX '||rdb$index_name ||' INACTIVE;' from rdb$indices where (rdb$system_flag is null or rdb$system_flag = 0) order by rdb$foreign_key nulls last into :stmt do EXECUTE STATEMENT :stmt; END!! SET TERM ; !! (not tested) > Alternatively, how safe is dropping/recreating the FKs on the fly? If nobody is using the database, I see no harm (but I'm not an expert). If it's just an import of one table I would only deactivate the indexes/primary keys/foreign keys of that table (and foreign keys pointing to that table). (Could also be done with a EXECUTE BLOCK and select of the correct rows) (just don't forget to activate them again afterwards :)) Grtz, Rik
Re: [firebird-support] Temporary disable FK constraint
On 06/11/15 16:15, Rik van Kekem r...@graficalc.nl [firebird-support] wrote: > Scott Morgan bl...@blueyonder.co.uk [firebird-support] wrote: >> In a FB2.5 system is there a way to temporarily disable foreign key >> constraints? i.e. something like 'ALTER INDEX ... INACTIVE' > Well, here is the way to activate indexes and foreign keys: > How to activate all indexes? http://www.firebirdfaq.org/faq274/ > > So doing the reverse should work too: > SET TERM !! ; > EXECUTE BLOCK AS > DECLARE VARIABLE stmt VARCHAR(1000); > BEGIN > for select 'ALTER INDEX '||rdb$index_name ||' INACTIVE;' > from rdb$indices > where (rdb$system_flag is null or rdb$system_flag = 0) > order by rdb$foreign_key nulls last > into :stmt > do EXECUTE STATEMENT :stmt; > END!! > SET TERM ; !! > > (not tested) You can't inactivate a FK constraint: SQL> ALTER INDEX FK_FOO INACTIVE; unsuccessful metadata update -MODIFY RDB$INDICES failed -action cancelled by trigger (2) to preserve data integrity -Cannot deactivate index used by an integrity constraint Scott
Re: [firebird-support] Temporary disable FK constraint
Scott Morgan bl...@blueyonder.co.uk [firebird-support] wrote: > You can't inactivate a FK constraint: B.T.W. This is a (still open) feature request: http://tracker.firebirdsql.org/browse/CORE-1924 http://stackoverflow.com/questions/1063617/how-can-i-temporarily-disable-all-constraints-in-a-table-in-firebird-2-1 Grtz, Rik
