Thread: referential integrity without trigger

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.

Alexander Presber

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