referential integrity without trigger

In article <302F3CB4-1087-4AAD-A23A-C9AE1C3FDFD9@weisshuhn.de>,
Alexander Presber <aljoscha@weisshuhn.de> writes:

Hello everybody,
Assuming I want to empty and refill table A (with roughly the same
content, preferrably in one transaction) and don't want to completely
empty a dependent table B but still keep referential integrity after
the commit.

Without disabling A's on-delete-trigger B will be be emptied on
commit, even when I inserted exactly the same data into A that I
deleted an instant before. That is because the trigger gets called on
commit, no matter if the deleted rows have "reappeared".

If I disable the trigger, My referential integrity is most likely
corrupted.
Is there a clever, general scheme to "recheck" and enforce foreign
key contraints, after the responsible triggers have been disabled and
reenabled?

I hope this makes sense to you.

Not quite? Why do you use an explicit trigger for checking
referential integrity? Can't you just use a foreign key with "ON
DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED"?

On Thu, 9 Feb 2006, Alexander Presber wrote:

Hello everybody,

Assuming I want to empty and refill table A (with roughly the same
content, preferrably in one transaction) and don't want to completely
empty a dependent table B but still keep referential integrity after
the commit.

Without disabling A's on-delete-trigger B will be be emptied on
commit, even when I inserted exactly the same data into A that I
deleted an instant before. That is because the trigger gets called on
commit, no matter if the deleted rows have "reappeared".

If I disable the trigger, My referential integrity is most likely
corrupted.
Is there a clever, general scheme to "recheck" and enforce foreign
key contraints, after the responsible triggers have been disabled and
reenabled?

Probably the easiest way to do these things is to drop the constraint
before, do stuff and re-add the constraint since that will check the
constraint at the add constraint time.

Alexander Presber <aljoscha@weisshuhn.de> writes:

Is there a clever, general scheme to "recheck" and enforce foreign
key contraints, after the responsible triggers have been disabled and
reenabled?

Drop the constraint (keep your fingers off the trigger, thank you ;-)).
Modify the master table. Re-create the constraint before committing.
ALTER TABLE ADD FOREIGN KEY does this about as fast as any ad-hoc idea
you might come up with.

regards, tom lane