On 11/28/19 5:25 AM, Nandakumar M wrote:
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.
Have you verified that the FK is not in the parent table and is just not
some index error/corruption?
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.
Assuming no corruption, how about?:
select distinct on(fk_field) fk_field from child_table where
child_table.fk_field not in (select parent_field from parent_table);
Also, is there any way to make sure the FK checking trigger can never
be disabled (so that such a case will never arise)?
Not sure that can happen as it is baked into existing code. For instance:
https://www.postgresql.org/docs/11/app-pgdump.html
"
--disable-triggers
This option is relevant only when creating a data-only dump. It
instructs pg_dump to include commands to temporarily disable triggers on
the target tables while the data is reloaded. Use this if you have
referential integrity checks or other triggers on the tables that you do
not want to invoke during data reload.
...
"
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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
