Subject How to deactivate referential integrity
Author csipm91
How to deactivate referential integrity (without dropping the indices)

Hi Jim,
In our project we encountered a problem that needs deactivation of
referential integrity on all tables.
The easiest way is just to drop all the foreign key constraints and
then re-create them. Meanwhile (when the integrity is down) we
perform some DML that would violate the integrity but summary at the
end the integrity is not broken.
I mean:
1. The foreign key constraints are ACTIVE.
2. The foreign key constraints are DOWN (ALTER ALL TABLES DROP
3. DML operations (some of them could violate ref.integr. if it was
4. The foreign key constraints are REBUILT UP (ALTER ALL TABLES ADD

Step 4 is quite slow 30min on Celeron400MHz/256RAM!!! and at that
time the database is not operational. We suppose that most of this
time goes for building index trees. So it may be shortened if the
referential constraints are not dropped fully - only deactivated some
way(indexes are alive but the checks for valid ForeignKey values are
Maybe via manipulating system tables. But we do not know how to
perform this "deactivation"

Please if you can give us some advice or example SQL operations.
Thanks in advance.

--- In IB-Architect@y..., Jim Starkey <jas@n...> wrote:
> At 09:09 PM 10/23/01 +0200, Martijn Tonies wrote:
> >Hi Jim,
> >
> >coming to this, is there an easy way to implement deactivation of
> >constraints? I believe (haven't looked into the source of FB) that
> >all constraints are enforced by triggers, right?
> >
> I could probably to made to by creating the implicit triggers with
> known classes. Probably would be a good idea.
> Netfrastructure, incidentally, while supporting (and using, heavily)
> primary and foreign keys, does not enforce referential integrity
> for a couple of reasons. First, good programs (and Netfrastructure
> programs are good or I won't let them at it) check that stuff as
> a matter of good person hygiene, so having the system repeat the
> check is a waste of time (though admittedly cache hits). Second,
> there are many cases where the index required would be grotesquely
> horrible (many, many duplicates).
> So, t'were it up to me, I toss in some DDL system to turn off
> referential integrity check on a key by key basis. Which would
> also probably obviate the need to deactivate constraints.
> So yes, may be, and no. May I be more obtuse?
> Jim Starkey