Hi,
I am using PG version 10.5.
Saw a table where we have foreign key defined but few thousand rows
violate the foreign key constraint.
I understand that one possibility of this happening is if we had
manually disabled the triggers that do FK integrity checks and re
enabled them afterwards. Is there any way to confirm if this has
happened -- does PG internally maintain something like an audit
history of DDL changes?
I am not sure if this is due to some mistake in our end or if there
are any known issues in PG 10.5 that would cause this.
I tried the ALTER TABLE ... VALIDATE CONSTRAINT for this particular FK
and it doesn't report any errors. The documentation pretty clearly
mentions that 'VALIDATE CONSTRAINT' is used only to check those
constraints created with 'NOT VALID' clause.
It might be useful to have an option to check integrity of any FK (not
just ones created with NOT VALID clause). Please let me know if there
is already any way to do this.
Also, is there any way to make sure the FK checking trigger can never
be disabled (so that such a case will never arise)?
How do I proceed from here - Do I just delete the inconsistent rows or
is there something more I have to do?
Thanks for your help.
Regards,
Nanda